VBA SQL Query ACCESS DB w/ LIKE "*" Operator

[bemp87]

Hi Community,

Looking for some help in solving a problem. I am getting back a NULL (no records it seems) when processing this query in VBA, but when i try it similarly in Acess it works the way it's supposed to. Not sure what i am doing wrong here. Any help would be appreciated.

VBA SQL String:

lastStatDate = "SELECT LAST(q.[Stat Date]) FROM QC_Data AS q WHERE q.[Stat Date] LIKE '" & "5*" & "'"

This returns NULL and i get a error saying "Invalid Use of Null" when trying to insert this into a message box using the RecordSet.Fields.(0).Value

Here is the MS Access Equivalent that returns a value

 SELECT LAST(q.[Stat Date]) FROM QC_Data AS q WHERE q.[Stat Date] LIKE "5*"

 

[alansidman]

strSql = "SELECT LAST(q.[Stat Date]) FROM QC_Data AS q WHERE q.[Stat Date] LIKE ""5*"""

 

[bemp87]

Still receiving an error, any idea what i am missing?

 

[alansidman]

When you click on Debug, what line of code is highlighted? Might be helpful if you showed all lines of code. When you google "run-time error 94" what do you find?

 

[Fazza]

using % instead of * for the wild card might be all that need change

I don't know the LAST function. if you still have issues, just try without it.

Not sure if this is an issue - I expect not - when aliasing the table I never use AS. But I do use it for field names - such as,
SELECT MAX(somefield) AS [NewNameOfFieldInResults] FROM longtablename q

that space in the field name might cause problems one day - any chance to change [Stat Date} to StatDate

 

[bemp87]

As always, you guys have helped me solve what was an extreme annoyance. I should have trusted my gut and used the modulo operator, but was caught trying to stick to what i found in a tutorial. This worked perfectly and is working as expected. Thanks!