SQL like with asterisk * works in MS-Access Query but not in VBA ADODB
I recently encountered a problem while writing a query for an excel application where I use ADODB to retrieve a list of items from the table based on a search key.
The query though works on Access when you write it in SQL but when I do the same with VBA ADODB it gave me error and that is when I realized after lots of trial and error that asterisk (*) does not work in VBA SQL so you have to replace asterisk (*) with percentile (%) symbol.
Here is an example of what I was doing.
select * from tbl_details where Region like '*H*';
This works fine when I run it in Access SQL window and it gives me the result but when I do the same thing in VBA it does not. I used an inputbox method to get the search key. For example like below was what I was using in VBA.
Dim k as string
k=inputbox("Enter the search key")
k="'*" & k & "*'"
Dim q as string
q="select * from tbl_details where Region like " & k & ";"
But I was getting error as it was not pulling up the records on these filters so I changed my code to below
Dim k as string
k=inputbox("Enter the search key")
k="'%" & k & "%'"
Dim q as string
q="select * from tbl_details where Region like " & k & ";"
This works now like a charm so anyone out there trying to make a SQL like statement in VBA remember you have to use % instead of *
For any excel, access, powerpoint or MS-word automation using VBA or SQL please whatsapp us at +91-998513004
or email us at info@upskilltechnologies.com
Reach out to us and free loads of your time by automating your daily tasks.
Comments