Syntax Error in Access VBA

Question

As far as I can tell, this strSQL statement is formatted correctly. I've used similar code without any issue in the past. However when I run this, I get a

Run Time Error 3075 - Syntax error (missing operator in query expression 'BucketID=CA.CAD.BAX.0.3.0.CY AND MaxofMarkAsofDate=#8/20/2014#'.

error message. This is frustratingly vague and I can't figure out what the issue is with my strSQL formatting.

  strSQL = "SELECT * FROM FXData WHERE BucketID=" & Forms!Correlation.cboCurve2.Value & " AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate "

Debug.Print strSQL

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)
Set rs2 = CurrentDb.OpenRecordset("HolderTable")

Include quotes before and after the string value which you compare to BucketID so that piece of your SQL statement looks like this: BucketID='CA.CAD.BAX.0.3.0.CY' Or switch to a parameter query, and you won't need to bother with those quotes or with the # date delimiters. — HansUp
If your first column (usually hidden) is the ID value then use the following: forms!correlation.cboCurve2.column(0) — LiamH

Thomas G Thomas G · Accepted Answer · 2016-04-26T16:09:14

This specific part of the error message:

BucketID=CA.CAD.BAX.0.3.0.CY

shows that your BucketID is a string and not a number, you should thus surround it with quotes :

strSQL = "SELECT * FROM FXData WHERE BucketID='" & Forms!Correlation.cboCurve2.Value & "' AND MaxOfMarkAsofDate=#" & MaxOfMarkAsofDate & "# ORDER BY MaxOfMarkasOfDate "

Syntax Error in Access VBA

1 Answers