Syntax error (missing operator) (Access 2k)

Topic

New Reply

Hello All

I’m trying to use an SQL string in VBA code, but I’m getting the error 3057 Syntax error (missing operator) due to the SQL I’ve entered. It’s going to be something like my date format, but I’ve tried what little I know and it hasn’t worked.

Here’s my SQL: –

strWordSQL = “SELECT * FROM PrimaryDataTbl WHERE DateReceived BETWEEN # ” & Me.StartDateTxt & ” # AND # ” & Me.EndDateTxt & ” # VendorNumber = ” & Me.VendorNumber

I’ve tried it without the # marks around the dates. Added them in the hopes it was as simple as having to put the ‘ before any text items referred to in a SQL string.

Using the de###### shows me that all three me.statements have the values in, using the SQL in the QBE window (with minor mods to suit the criteria) gives me the results I want……

Please, help while I still have a little hair left that isn’t either grey or on the desk

Thanks

Ian

Reply | Quote

Replies

Have you got a space after the first # and before the second # ? ie. # ” and ” #

If so it will be looking for # 01/09/2004 # rather than #01/09/2004#

Reply | Quote

Have you got a space after the first # and before the second # ? ie. # ” and ” #

If so it will be looking for # 01/09/2004 # rather than #01/09/2004#

Reply | Quote

What if you use :
strWordSQL = “SELECT * FROM PrimaryDataTbl WHERE DateReceived BETWEEN # ” & Me.StartDateTxt & ” # AND # ” & Me.EndDateTxt & ” # And VendorNumber = ” & Me.VendorNumber

Reply | Quote

What if you use :
strWordSQL = “SELECT * FROM PrimaryDataTbl WHERE DateReceived BETWEEN # ” & Me.StartDateTxt & ” # AND # ” & Me.EndDateTxt & ” # And VendorNumber = ” & Me.VendorNumber

Reply | Quote

Ian,

Is VendorNumber = ” & Me.VendorNumber part of your strWordSQL statement? Also, Does the strWordSQL statement wrap as shown in your post or is it on one line?

If wrapping the strSql string, then you would need something like this

strWordSql = “Select * From” _
& ” SomeTable;”

You will also need to add an ; at the end of your statement, for example,
strWordSql = “Select * from SomeTable;”

Reply | Quote

Francois

As yet I don’t what’s different between your SQL string and mine, but whatever it is it works another learning opportunity provided by the good folks of Woody’s lounge

Thanks

Ian

Reply | Quote

You forget the And before VendorNumber.
Between two conditions you have an AND or an OR.
Gald it work for you now.

Reply | Quote

You forget the And before VendorNumber.
Between two conditions you have an AND or an OR.
Gald it work for you now.

Reply | Quote

Francois

As yet I don’t what’s different between your SQL string and mine, but whatever it is it works another learning opportunity provided by the good folks of Woody’s lounge

Thanks

Ian

Reply | Quote

Ian,

Is VendorNumber = ” & Me.VendorNumber part of your strWordSQL statement? Also, Does the strWordSQL statement wrap as shown in your post or is it on one line?

If wrapping the strSql string, then you would need something like this

strWordSql = “Select * From” _
& ” SomeTable;”

You will also need to add an ; at the end of your statement, for example,
strWordSql = “Select * from SomeTable;”

Reply | Quote