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.


#ExcelFreelancer, #Consulting, #ExcelAutomation, #SmallBusinessApplications #OfficeAutomation #Reporting #TemplateDesigns

Featured Posts
Recent Posts
Search By Tags
No tags yet.
Follow Us
  • Facebook Classic
  • Twitter Classic
  • Google Classic