Text case search: is upper or is lower

[David E. Jones]

Is there any function in Access (using AC 2003) to test for the case of
a text field? For example, I want to find records that have only upper
case in a field.

Or, can I create an expression that converts the wanted field to
uppercase (UCase) and then matches to the original field in a case
sensitive way?

When I used the Alpha 4 database program there were functions for
"isupper" and "islower" but I don't see any equivalent in Access.

Thanks.

David Jones

 

[David Lloyd]

David:

One alternative would be your second choice. You can use the StrComp
function to compare two strings. Using the vbBinaryCompare parameter allows
for a case sensitive comparison.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Is there any function in Access (using AC 2003) to test for the case of
a text field? For example, I want to find records that have only upper
case in a field.

Or, can I create an expression that converts the wanted field to
uppercase (UCase) and then matches to the original field in a case
sensitive way?

When I used the Alpha 4 database program there were functions for
"isupper" and "islower" but I don't see any equivalent in Access.

Thanks.

David Jones

 

[John Vinson]

Is there any function in Access (using AC 2003) to test for the case of
a text field? For example, I want to find records that have only upper
case in a field.

It's not trivial. The StrComp() function (see the online Help by
opening the VBA editor and searching for StrComp) will compare two
text strings and returning 0 if they are identical, and a nonzero
value if they differ: so you can use

WHERE StrComp([fieldname], UCase([fieldname]), 0) = 0

to find all records where fieldname is in all caps.

Or, can I create an expression that converts the wanted field to
uppercase (UCase) and then matches to the original field in a case
sensitive way?

When I used the Alpha 4 database program there were functions for
"isupper" and "islower" but I don't see any equivalent in Access.

Well... Access is Access, Alpha 4 is Alpha 4. They are different
programs with different syntax.

John W. Vinson[MVP]

 

[David E. Jones]

Is there any function in Access (using AC 2003) to test for the case of
a text field? For example, I want to find records that have only upper
case in a field.

It's not trivial. The StrComp() function (see the online Help by
opening the VBA editor and searching for StrComp) will compare two
text strings and returning 0 if they are identical, and a nonzero
value if they differ: so you can use

WHERE StrComp([fieldname], UCase([fieldname]), 0) = 0

to find all records where fieldname is in all caps.

Or, can I create an expression that converts the wanted field to
uppercase (UCase) and then matches to the original field in a case
sensitive way?

When I used the Alpha 4 database program there were functions for
"isupper" and "islower" but I don't see any equivalent in Access.

Well... Access is Access, Alpha 4 is Alpha 4. They are different
programs with different syntax.

John W. Vinson[MVP]

David and John,

The StrComp function did the trick. Thank you both very much for the advice.