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
– HansUp, Commented Apr 26, 2016 at 15:09
If your first column (usually hidden) is the ID value then use the following: forms!correlation.cboCurve2.column(0) — LiamH
– LiamH, Commented Apr 26, 2016 at 15:14

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

2

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 "

answered Apr 26, 2016 at 16:09

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

beeba Over a year ago

thanks - I made that change, but now it tells me that :RUN-TIME ERROR '3078': The Microsoft Access database engine cannot find the input table or query 'False'. Make sure it exists and that its name is spelled correctly." I do not call anything by the name of "false" anywhere in this code so I am not sure what this references

Collectives™ on Stack Overflow

Syntax Error in Access VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related