Access query utilising wildcards and non case-sensitive

mikeymay

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

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
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,415
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
52,066
Office Version
365
Platform
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,415
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,415
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
1,803
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,089,218
Messages
5,406,923
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top