Case Sensitive Query

[Guest]

How do I create a query on a field that returns records that do not match a
case critera, in this case all caps. I have a field that has SFP in it but
the data must be in caps and I would like a list of those entered incorrectly
as sfp. I have over 200,000 records and it would take to long otherwise.

Thanks, JLD

 

[Douglas J Steele]

Assuming you're running the query from within Access, you can use the
StrComp function (although it will make the query quite slow).

StrComp(String1, String2, 0) will return 0 if the two strings are the same
(case and all), or non-zero otherwise.

Note that while the StrComp function has named constants that you can use as
the 3rd parameter (vbBinaryCompare, vbDatabaseCompare or vbTextCompare), you
can't use the named constants in a query.

 

[Bill Edwards]

SELECT *
FROM tblName
WHERE asc(left(FieldName,1)) = 115
OR asc(mid(FieldName,2,1)) = 102
OR asc(right(FieldName,1)) = 112