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

  • Thread starter Thread starter LarissaR
  • Start date Start date
L

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!
 
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!
Click to expand...

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.
 
Access is not case sensitive like some other databases. Therefore searching
for

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

will return the same records.
 
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!

John W. Vinson said:
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!
Click to expand...

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.
Click to expand...
 
Back
Top