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

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
100
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:
VBA Code:
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
VBA Code:
 SELECT LAST(q.[Stat Date]) FROM QC_Data AS q WHERE q.[Stat Date] LIKE "5*"
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,190
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
strSql = "SELECT LAST(q.[Stat Date]) FROM QC_Data AS q WHERE q.[Stat Date] LIKE ""5*"""
 

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
100
1593124996580.png

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

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,190
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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
 
Last edited:

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
100
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,942
Messages
5,639,106
Members
417,072
Latest member
JaimeDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top