• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 74
  • Last Modified:

Expected end of statement.

The following code in Access 2016 gives me an "expected end of statement error message".
SQL_PriFam_Qry = "UPDATE ClientInfo " & _
            "SET ClientInfo.PrimaryClientName_C = Nz(DLookUp("ClientID_FK","ProspectiveClients","ProspectID=" & [PrimaryClientName]),0), " & _
            "   ClientInfo.FamilyMember_C = Nz(DLookup("ClientID_FK", "ProspectiveClients", "ProspectID=" & [FamilyMember]), 0) " & _
            "WHERE (((ClientInfo.ClientID)=1494))"
1 Solution
Ryan ChongCommented:
you may try:

SQL_PriFam_Qry = "UPDATE ClientInfo " & _
            "SET ClientInfo.PrimaryClientName_C = Nz(DLookUp(""ClientID_FK"",""ProspectiveClients"",""ProspectID="" & [PrimaryClientName]),0), " & _
            "   ClientInfo.FamilyMember_C = Nz(DLookup(""ClientID_FK"", ""ProspectiveClients"", ""ProspectID="" & [FamilyMember]), 0) " & _
            "WHERE (((ClientInfo.ClientID)=1494))"

Open in new window

When you build SQL in VBA (which I rarely do since I prefer working with querydefs), you should always print it to the debug window to see what your actual string looks like.  This will show you any syntax errors.  If after you print the string, you still don't see the error, copy the string and paste it into the QBE in SQL View and see what Access has to say.  Frequently you'll get a better error message.

Put a stop on the line that executes the query.  When the code stops, print SQL_PriFam_Qry to the window.
VGuerra67Author Commented:
Thanks that works
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now