Access query utilising wildcards and non case-sensitive

[mikeymay]

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

 

[Joe4]

Access does not care about case, so you don't need to worry about that. Just use:
Like "*ma*"
to cover all bases.

 

[mikeymay]

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

"SELECT PIRef FROM PIData WHERE PIRef LIKE '*" & strPartial & "*' ORDER BY PIRef Asc"

where strPartial = "583"

Again, TIA

 

[Joe4]

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]

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]

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]

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.