case sensitive LIKE search

  • Thread starter Thread starter Abhishek Madas via AccessMonster.com
  • Start date Start date
A

Abhishek Madas via AccessMonster.com

I am facing a problem writing a query.
The problem is I have a column with different formulas of chemical
molecules. for eg CH3Cl
CbF-(Cb2)
CBF-(Ca)
Now I wrote this query
SELECT *from tbl where MolFormula LIKE 'Cb%'

This returns me row 2 and row 3 whereas I just need row 2 to be returned.
I am using MS Access. Thanks for looking into it.
 
I am facing a problem writing a query.
The problem is I have a column with different formulas of chemical
molecules. for eg CH3Cl
CbF-(Cb2)
CBF-(Ca)
Now I wrote this query
SELECT *from tbl where MolFormula LIKE 'Cb%'

This returns me row 2 and row 3 whereas I just need row 2 to be returned.
I am using MS Access. Thanks for looking into it.
Click to expand...

Access queries are inherently case-insensitive. You need to use the
StrComp() or InStr() function to get case sensitivity.

Try

SELECT *
FROM tbl
WHERE MolFormula LIKE "Cb*"
AND InStr([MolFormula], "Cb", 0) = 1;

Note that the Access/JET wildcard character is * rather than %.

I'm including the LIKE clause to at least filter the candidates down
to values starting with cb, cB, Cb, or CB so the timeconsuming
function call doesn't happen as often.

John W. Vinson[MVP]
 
ACCESS / Jet queries are not case-sensitive directly. (You're using the %
wildcard here, so you're using ADO as the library?)

You could use a calculated field in the query as the "test" (the last
argument of InStr makes the comparison a "binary" comparison, where case
will be respected):

SELECT * from tbl where
InStr(1,[MolFormula], 'Cb%', 0) > 0;


Otherwise, you will need to use VBA code to do the searching for you,
because in the code you can convert the "entered" characters to their ASCII
character values and then search based on them; or by using InStr function
to do a binary comparison, or
 
Back
Top