How to query a text string when case is in upper and lower

[LarissaR]

I have access to a database that shows a list of department names in our
company. The format for entering the names wasn't standardized, so the text
can have combinations of upper and lower case. I know in SQL it's easy to
say, "look for this word, but pretend everything you see is in lower case."
Is there a similar way to do that in a regular Access query? I need to run a
partial match, so I'll be using the "Like" statement.
Thanks for your time!

 

[John W. Vinson]

I have access to a database that shows a list of department names in our
company. The format for entering the names wasn't standardized, so the text
can have combinations of upper and lower case. I know in SQL it's easy to
say, "look for this word, but pretend everything you see is in lower case."
Is there a similar way to do that in a regular Access query? I need to run a
partial match, so I'll be using the "Like" statement.
Thanks for your time!

Access queries are not case sensitive. LIKE "*fred*" will find records
containing fred, Fred, or FRED with no special coding needed.

If you want case sensitivity, or if the query is being run by SQL/Server or
another engine which is case sensitive, you may need to use

WHERE LCase([fieldname]) = LCase([Enter criterion:])

but that will certainly give a performance hit.

 

[Jerry Whittle]

Access is not case sensitive like some other databases. Therefore searching
for

Like "ABC" & "*"
or
Like "abc" & "*"

will return the same records.

 

[LarissaR]

Ah! Thanks! The table I'm querying is one I've linked through an ODBC data
source, and from your comment below it sounds like that might be the issue. I
put LCase([care_unit_dsc]) Like LCase("*intensive*") Or Like "*ICU*" into the
criteria line, and that returned the results I expected. Yipee!

I have access to a database that shows a list of department names in our
company. The format for entering the names wasn't standardized, so the text
can have combinations of upper and lower case. I know in SQL it's easy to
say, "look for this word, but pretend everything you see is in lower case."
Is there a similar way to do that in a regular Access query? I need to run a
partial match, so I'll be using the "Like" statement.
Thanks for your time!

Access queries are not case sensitive. LIKE "*fred*" will find records
containing fred, Fred, or FRED with no special coding needed.

If you want case sensitivity, or if the query is being run by SQL/Server or
another engine which is case sensitive, you may need to use

WHERE LCase([fieldname]) = LCase([Enter criterion:])

but that will certainly give a performance hit.