Access query utilising wildcards and non case-sensitive

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
Access does not care about case, so you don't need to worry about that. Just use:
Like "*ma*"
to cover all bases.
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,079
Office Version
  1. 365
Platform
  1. Windows
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"
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490

ADVERTISEMENT

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.
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,490
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
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,240
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,704
Messages
5,707,970
Members
421,538
Latest member
Krisco

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