CODEWITHSUNDEEP
sqlms-accessvba
billmann
billmann

Reputation: 57

Why is my VBA/SQL query say I am missing an operator?

Can anyone spot the syntax error? I feel like I have all the operators for the OpenRecordset method that are necessary.

The error says "Syntax error (missing operator) in query expression '(Date: = 10/31/2014)'.

the class variable is equal to the string "007-14 A"

Below is the line of code where the error is coming from:

Set rec = db.OpenRecordset("SELECT '" & class & "' FROM tblCalendar WHERE (Date: = 10/31/2014)")

Do I have the wrong quotes around the wrong things or could it have to do with the semicolon in the title of the field Date?

class is a field I want to select. So the actual field title is 007-14 A.

Upvotes: 0

Views: 1369

Answers (1)

Christian Hayter
Christian Hayter

Reputation: 31071

You have 3 errors here. '007-14 A' and Date: are not legal column names, and 10/31/2014 is not a legal date literal value.

Wrap illegal column names in square brackets, e.g. [007-14 A] and [Date:]

Format the date literal value as #10/31/2014#

If that date literal format does not work, try #31 Oct 2014#

NOTE: MS Access uses the JET engine SQL dialect, which looks suspiciously similar to VB (I guess it's leveraging the VBA engine for parsing under the hood). Generally speaking, you can use the same literal syntax and built-in functions across VB6, VBA, VBScript, ADO disconnected recordset filters, and MS Access queries. However, there are subtle differences which can trip you up.

Upvotes: 7

Related Questions