Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    SQL doesn't work (XP)

    The following lines of code get an error message - "syntax error in query. Incomplete query clause"

    sqlExistingRecord = "SELECT * FROM """ & strTableName & """ WHERE """ & strIDField & """ = """ & strGetID & """"

    Debug.Print sqlExistingRecord ' prints - SELECT * FROM "tblDocument" WHERE "Document_ID" = "20070513000100000006"

    Set rstExistingRecord = CurrentDb.OpenRecordset(sqlExistingRecord, dbOpenSnapshot)

    Maybe I've been staring at it for far too long and just need someone with fresh eyes to look at it.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL doesn't work (XP)

    The problem is that you shouldn't have quotes in the SQL Statement around the name of the table or the field.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL doesn't work (XP)

    So I need to end up with -
    SELECT * FROM tblDocument WHERE Document_ID = "20070513000100000012"
    - instead of
    SELECT * FROM "tblDocument" WHERE "Document_ID" = "20070513000100000012"

    but how do I get that when I am feeding the SQL with variables for the name of the table and Field?

    do I have too many quotes in this line? -
    sqlExistingRecord = "SELECT * FROM """ & strTableName & """ WHERE """ & strIDField & """ = """ & strGetID & """"

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL doesn't work (XP)

    Yes, you do. Double quotes delimit a string of text. You don't need them around string variables if you just want to concatenate their values. You do need for for string comparisons to let the query engine know that the value being tested is, in fact, a string value. Here is the corrected line:

    <code>sqlExistingRecord = "SELECT * FROM " & strTableName & " WHERE " & strIDField & " = """ & strGetID & """"</code>

    An alternative when using Access SQL, is this:

    <code>sqlExistingRecord = "SELECT * FROM " & strTableName & " WHERE " & strIDField & " = '" & strGetID & "'"</code>
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL doesn't work (XP)

    Ah, now I understand - I was over complicating things!
    Thanks Charlotte

Posting Permissions

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