Access query utilising wildcards and non case-sensitive

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
I am trying to run a query on a table to return all field values that contain a text string.

E.g. If the user wants to return all values that contain 'ma' I would need this to return all values that
Start with ma in ANY case combination - ma*, MA*, Ma*, mA*
Contain ma in ANY case combination - *ma*, *MA*, *Ma*, *mA*
End with ma in ANY case combination - *ma, *MA, *Ma, *mA

The query text I am using at present is - "SELECT FullName FROM PIData WHERE FullName LIKE 'ma*' ORDER BY FullName Asc" where I am checking any values that start with ma

The above query is returning 0 records and I have 1 record that meets this criteria.


TIA
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Access does not care about case, so you don't need to worry about that. Just use:
Like "*ma*"
to cover all bases.
 
Upvote 0
Thanks! That seemed to work...not sure why I didn't get that earlier but hey ho!

I now have a similar issue with looking for a string in another field that contains numbers.

The field is set as a string in Access but has a prefrix of 'PI' and then 6 digits, e.g. 005834. The full field value as an example would be PI005834.

I need to also do a search on a partial string such as 5834, or 005834.

This is what I am using which returns zero records
VBA Code:
"SELECT PIRef FROM PIData WHERE PIRef LIKE '*" & strPartial & "*' ORDER BY PIRef Asc"
where strPartial = "583"

Again, TIA
 
Upvote 0
I think you are missing some single quote marks.
Since you are using double-quotes around your entire text string for the expression you are building, your want single quotes around BOTH sides of *, like '*'.
So try:
"SELECT PIRef FROM PIData WHERE PIRef LIKE '*'" & strPartial & "'*' ORDER BY PIRef Asc"
 
Upvote 0
I think you are missing some single quote marks.
Since you are using double-quotes around your entire text string for the expression you are building, your want single quotes around BOTH sides of *, like '*'.
So try:
"SELECT PIRef FROM PIData WHERE PIRef LIKE '*'" & strPartial & "'*' ORDER BY PIRef Asc"
I've tried that and it doesn't like the * enclosed in the quotes.

I would have thought the solution you provided for the text question I had initially would work as this field is set as a string.
 
Upvote 0
So I found what seems to be a solution

SELECT PIRef FROM PIData WHERE Instr(PIRef, " & strPartial & ") ORDER BY PIRef Asc

Which turns into
SELECT PIRef FROM PIData WHERE Instr(PIRef, 834) ORDER BY PIRef Asc

When sent to Access
 
Upvote 0
I think Like operator would have worked if it was
LIKE ""*" & strPartial & "*""

but Instr is OK too IMO. I wonder if there is a speed difference between using the LIKE operator as opposed to calling a function if there are a lot of records involved. In your case, probably no difference because your search pattern is simple.
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,432
Members
448,573
Latest member
BEDE

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
Back
Top