Syntax error (missing operator) in query expression...

[DuncanMcCloud]

Hi All,

I am getting the following MS Access error:

Syntax error (missing operator) in query expression '(((BOQ.Description) =Site Establishment Dismantle))'

For the following VBA statement:

Dim name As String
name = "Site Establishment Dismantle"
Set rs1 = db1.OpenRecordset("select BOQ.Description from BOQ where " & " (((BOQ.Description) =" & Qu & name & Qu & "))", DB_OPEN_DYNASET)

The statement is in one line.

BOQ is a Table and Description is a Text Field Name.

I would like to select the record in the table that matches name string and then be able to access other field names of that record.

My alternative is using a do loop to search through the entire table...I would like to cut down on this wasted cpu overhead...

I am obviously doing something wrong. Please can anyone help?

Thanks!

[Stewart Ross]

The error message is showing that the string literal you want to use in the WHERE clause is present, but it is not enclosed in single-quotes - hence the error. You have a constant or variable Qu defined for this purpose, but whatever it contains it is not a single quote:

where " & " (((BOQ.Description) =" & Qu & name & Qu & ")

You can simply put the single quotes directly into the SQL string before and after the inclusion of the string literal, like this:

where Description ='" & name & "';",  DB_OPEN_DYNASET))

I have also removed the unnecessary bracketing and table-name qualification from the where clause, something which is usually added by the query designer and inadvertently copied if you use the SQL created by the query designer as a guide.

-Stewart

[DuncanMcCloud]

Thanks Stewart.

I have the following to add:

User:
check your bracketing

Dim name As String
dim strSQL as string
name = "Site Establishment Dismantle"
strSQL = "SELECT Description from BOQ WHERE Description='"  & name & "'"
Set rs1 = db1.OpenRecordset(strSQL, DB_OPEN_DYNASET)

just guessing qu is a variable defiend as ' or ".. you coudl use the CHR function if you prefer

..although why you would want to retrieve the description from the table when you already know the description AND are usign it as the filter in the WHERE clause baffles me

Me:
Thanks, it passes now, but now I have a new error

Set rs1 = db1.OpenRecordset("select Description from BOQ where Description ='" & name & "'", DB_OPEN_DYNASET)
MsgBox (rs1!Rate)

Access returns saying "Item not found in this collection." when it executes the last line. The field is there and it is populated in the table...
I have tried it with other Descriptions & field names as well, same error.

User:

Your query is:

select Description from BOQ where...

Which means that Description will be the only column available in the resulting Recordset.

If you want to have the column Rate included in the recordset, it must be present in the SELECT column list of the query (you only receive what you asked for):

SELECT Description, Rate from BOQ where...

Problem is solved,
Thank you

[Stewart Ross]

Please note also that all you are SELECTing is the string you have compared - the description. This is not likely to be of much assistance to you.

It is easier to see such logic errors if you split your code up a little and use a separate string for the SQL, like this:

Dim strSQL as String
strSQL = "SELECT Description from BOQ "
strSQL = strSQL & "WHERE Description = '" & name & "';" 
Set rs1 = db1.OpenRecordset(strSQL, DB_OPEN_DYNASET)

-Stewart

[DuncanMcCloud]

Thanks for the tip Stewart. It is something I would have to get used to :)

[NeoPa]

Good tips from Stewart (of course) but I'd add an alternative that you might use instead. It follows the same main principles though :

Dim strSQL As String
strSQL = "SELECT Description from BOQ " & _
         "WHERE Description = '" & name & "';"

Use of the continuation character (_) enables you to see what you're working with more clearly and easily.

Here's something else to check on when dealing with any SQL problems - How to Debug SQL String - I expect you'll find this helps you solve many of them without the need for questions (Though we're here for any that need it).

PS. May I suggest that post #2 is a prime candidate for the Best Answer button for this thread ;-)

[DuncanMcCloud]

Thank you for the tips :)