WHERE Statement for OpenRecordset

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Hey,

I am trying to open a recordset of a query and I received the too few
parameters error, which I found out was you the WHERE statement received data
from 2 controls on a form. If my understanding is correct its becuase the
OpenRecordset parameter requires a string and the data referencing the
controls should not form part of the string value.

So now I a defining a String to be used as the OpenRecordset parameter but I
am having issues, below if the SQL version:

WHERE (((tblPerson.[FIRST NAME]) Like "*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_FIRST_NAME] & "*") AND
((tblPerson.[LAST NAME]) Like "*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_LAST_NAME] & "*"));

A mess I know, but is anyone able to help me convert it so that I can add it
to my SQL string?

Thanks
Simon
 
Perhaps something like this:
strSql = "SELECT tblPerson.* FROM tblPerson WHERE (tblPerson.[FIRST NAME]
Like ""*" & [forms]![frmHOTY08RecordOfEntry]![ROE_FIRST_NAME] & "*"") AND
(tblPerson.[LAST NAME] Like ""*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_LAST_NAME] & "*"");"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html
 
Thanks Allen,
The String is created succesfully and reading you linked helped, when I was
tring to work it out earlier I was using far to many quotes.

However, I now have another error regarding the DB engine not being able to
find the input query and to check that it exists. The crazy thing is that I
have taken the SQL String and created a query from it and the query execute
perfectly. I shall now dig around for info on this issue.

Thanks
Simon

Allen Browne said:
Perhaps something like this:
strSql = "SELECT tblPerson.* FROM tblPerson WHERE (tblPerson.[FIRST NAME]
Like ""*" & [forms]![frmHOTY08RecordOfEntry]![ROE_FIRST_NAME] & "*"") AND
(tblPerson.[LAST NAME] Like ""*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_LAST_NAME] & "*"");"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Simon said:
I am trying to open a recordset of a query and I received the too few
parameters error, which I found out was you the WHERE statement received
data
from 2 controls on a form. If my understanding is correct its becuase the
OpenRecordset parameter requires a string and the data referencing the
controls should not form part of the string value.

So now I a defining a String to be used as the OpenRecordset parameter but
I
am having issues, below if the SQL version:

WHERE (((tblPerson.[FIRST NAME]) Like "*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_FIRST_NAME] & "*") AND
((tblPerson.[LAST NAME]) Like "*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_LAST_NAME] & "*"));

A mess I know, but is anyone able to help me convert it so that I can add
it
to my SQL string?

Thanks
Simon
Click to expand...
Click to expand...
 
Post the code you are using for OpenRecordset().

If you need an example to follow:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

If the string is not right, you can add this line before the OpenRecordset
one:
Debug.Print strSql
When it fails, press Ctrl+G to open the Immediate window.
Copy what you see there, and paste it into a new query in SQL View.
Compare it to a query you mock up for the purpose, and see if you can pick
what's wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Simon said:
Thanks Allen,
The String is created succesfully and reading you linked helped, when I
was
tring to work it out earlier I was using far to many quotes.

However, I now have another error regarding the DB engine not being able
to
find the input query and to check that it exists. The crazy thing is that
I
have taken the SQL String and created a query from it and the query
execute
perfectly. I shall now dig around for info on this issue.

Thanks
Simon

Allen Browne said:
Perhaps something like this:
strSql = "SELECT tblPerson.* FROM tblPerson WHERE (tblPerson.[FIRST NAME]
Like ""*" & [forms]![frmHOTY08RecordOfEntry]![ROE_FIRST_NAME] & "*"") AND
(tblPerson.[LAST NAME] Like ""*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_LAST_NAME] & "*"");"

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Simon said:
I am trying to open a recordset of a query and I received the too few
parameters error, which I found out was you the WHERE statement
received
data
from 2 controls on a form. If my understanding is correct its becuase
the
OpenRecordset parameter requires a string and the data referencing the
controls should not form part of the string value.

So now I a defining a String to be used as the OpenRecordset parameter
but
I
am having issues, below if the SQL version:

WHERE (((tblPerson.[FIRST NAME]) Like "*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_FIRST_NAME] & "*") AND
((tblPerson.[LAST NAME]) Like "*" &
[forms]![frmHOTY08RecordOfEntry]![ROE_LAST_NAME] & "*"));

A mess I know, but is anyone able to help me convert it so that I can
add
it
to my SQL string?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top