conditional string conversion to mixed case

[Guest]

Is it possible to apply StrConv([field],3) query only to those fields where
contents are in all lower case, all upper case, or all lower/upper with
hyphen?

 

[Michel Walsh]

Somehow, yes, but you must not use = since the equality will be insensitive
to the case. You have to use StrComp. Note that StrComp compares the two
string and RETURN -1, 0 or +1, it does NOT true or false. It is also
strongly suggested you supply the third argument as 0.


WHERE 0 = StrComp(fieldName, UCASE(fieldName), 0)



should locate records that have their values under the said field, all in
upper case.


Vanderghast, Access MVP

 

[John Spencer]

You don't need to worry about the dash being present or absent since the
dash doesn't have any case associated with it.
So basically, you want to show the field converted if the field is all upper
case or all lower case, but if it is mixed case then show the field as is

I think the following should work.

IIF(StrComp(Ucase(Field),Field,0) = 0 OR StrComp(LCase(Field),Field) = 0,
StrConv(Field,3), Field)

StrComp will do a binary compare if the third argument is zero and returns 0
if there is an exact match. So if you force the value to all upper case and
compare it to itself you will get zero if the value is all upper case.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..