Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    5

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

    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:

    Code:
    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!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,424
    check your bracketing
    Code:
    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
    Last edited by healdem; 10-20-11 at 03:48.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    5
    Thanks, it passes now, but now I have a new error

    Code:
    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.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,251
    Your query is:
    Code:
    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):
    Code:
    SELECT Description, Rate from BOQ where...
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    5
    Thank you very much healdem and Sinndho.

    My problems are solved. It makes sense now.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,251
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •