sql in vba line of code error

[ECEstudent]

SQL line of code error

Does anyone know what's wrong with this line of code? It keeps giving me a syntax error...

Set rst = CurrentDb.OpenRecordset( _
"Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*'")AND (((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#)) ")

 

[ECEstudent]

Does anyone know what's wrong with this line of code? It keeps giving me a syntax error...

Set rst = CurrentDb.OpenRecordset( _
"Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*'")AND (((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#)) ")

 

[SOS]

Looks like you need a space after the paren just before the word AND

 

[spikepl]

What you need is a different way of coding - read the link very carefully: http://www.baldyweb.com/ImmediateWindow.htm

 

[pbaldy]

Re: SQL line of code error

Here's a commonly used tool to debug SQL in code:

http://www.baldyweb.com/ImmediateWindow.htm

If you don't spot the problem, post the final SQL here, along with the data types of the fields.

 

[SOS]

Duplicate thread. Original:
http://www.access-programmers.co.uk/forums/showthread.php?t=248741

 

[billmeye]

Re: SQL line of code error

Try:

Set rst = CurrentDb.OpenRecordset( _
"SELECT * FROM CalculateTotal WHERE (((Structure) Like ""*"" & '" & u & "' & ""*"") AND ((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#));"

I changed your Like statement and added an opening parenthesis after WHERE.

 

[ECEstudent]

Ok. Thanks. Figured it out!

Supposed to look like this:

Set rst = CurrentDb.OpenRecordset( _
"Select * from CalculateTotal WHERE ((Structure) Like'*" & u & "*')AND (((ShipDate) BETWEEN #09/30/2001# AND #10/01/2012#)) ")

 

[pbaldy]

I merged your duplicate threads since they both had responses. In the future, please don't post the same question twice.