Make search case insensitive

[detrie]

Access 2003

Trying to filter a form based on a field with wildcard
My form has a txtCustFilter control where a customer's name cane be entered in part or whole
The Customer's name is in PCCustomerName

This code works but, I'd like to make it case insensitive

Dim strFilter As String

            strFilter = "[PCCUSTOMERNAME]  LIKE ""*" & Me.txtCustFilter & "*"""

Me.Filter = strFilter
Me.FilterOn = True

 

[CJ_London]

Access isn't case sensitive so not sure what your point is - do you mean you want to make it case sensitive - i.e. recognise capitals?

 

[detrie]

A Customer name may be stored as ABC , abc or aBc
If bc is entered into txtCustFilter I'd like the filter to find all three entries

My code only returns abc

 

[CJ_London]

it should do - the only thing I would change to your code is

strFilter = "[PCCUSTOMERNAME]  LIKE[COLOR=red] "'*"[/COLOR] & Me.txtCustFilter &[COLOR=red] "*'"[/COLOR]

but I would not expect it to make a difference - Access is not sensitive to case.

Might be an idea to explain in more detail what you are doing and post you entire SQL

 

[pbaldy]

One potential wrinkle. Is there an Option Compare statement at the top of this module, and if so what is it?

 

[detrie]

Option Compare Database

 

[pbaldy]

Then I'm with CJ and I'll get out of the way. With that option, it should be case insensitive already.

 

[spikepl]

Actually, you better keep

 LIKE ""*" & Me.txtCustFilter & "*"""

rather than the suggested

 LIKE '*" & Me.txtCustFilter & "*'"

because the last one will fail for a O'Donnel or a d'Artagnan

As to your issue: did you check your data for leading spaces? Use the Trim function to strip off such things

 

[detrie]

the record Im working with has no leading spaces

 

[MarlaC]

Is it possible that the records have different characters altogether, not just ABC vs. abc, but ÀBC vs. abc? That could make the difference, because Access is not case sensitive regarding filter strings.