Case-sensative query

[AccessChick]

Hello, I am trying to query a text field to return the count of entries by
case, ie count of Mixed Case, lower case and UPPER CASE entries.

Is this at all possible?

 

[Michel Walsh]

Not without writing a VBA function, I am afraid.

Imagine a VBA function, in a standard module, like:

============================
Public Function KeyCase(x As String) As Long
Dim i As Long
Dim n As Long: n = Len(x) ' should be <= 31

Dim cumul As Long: cumul = 0
Dim temp As String ' a character at a time

For i = 1 To n
temp = Mid(x, i, 1)
cumul = 2 * cumul - (0 = StrComp(temp, UCase(temp),
vbBinaryCompare))
Next i
KeyCase = cumul

End Function
===============================

you can then, in a query, use:



SELECT fieldOfName, COUNT(*)
FROM tableNameHere
GROUP BY KeyCase(fieldOfName), fieldOfName



*** Note that KeyCase will be in error if the string length exceed 31
characters.


Vanderghast, Access MVP


..

 

[KARL DEWEY]

If you are talking about the first letter in a field then use this --
CaseCheck: Asc([YourField])
65 - 90 is upper case and 97 - 122 lower case.

 

[John Spencer]

You could use ucase, lCase, and strcomp, but it may get messy. You can
try the following UNTESTED SQL statement.

StrComp(SomeField, Ucase(someField),0) = 0 then its all uppercase
StrComp(SomeField,LCase(SomeField),0) = 0 then its all lowercase

StrComp(SomeField, Ucase(someField),0) <> 0 AND StrComp(SomeField,
LCase(someField),0) <> 0 Then it is mixed case

SELECT SomeField
, Abs(Sum(StrComp(SomeField, Ucase(someField),0) = 0)) as UcaseCount
, Abs(Sum(StrComp(SomeField, LCase(someField),0) = 0)) as LCaseCount
, Abs(Sum(StrComp(SomeField, Ucase(someField),0) <> 0 AND
StrComp(SomeField, LCase(someField),0) <> 0)) as MixedCount
FROM YourTable
GROUP BY SomeField

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

 

[John W. Vinson]

Hello, I am trying to query a text field to return the count of entries by
case, ie count of Mixed Case, lower case and UPPER CASE entries.

Is this at all possible?

If I understand you create a Query with three calculated fields:

Upper: IIF(StrComp([field], UCase([field]), 0) = 0, 1, 0)
Lower: IIF(StrComp([field], LCase([field]), 0) = 0, 1, 0)
Mixed: IIF(StrComp([field], UCase([field]), 0) = 0 OR StrComp([field],
LCase([field]), 0) = 0, 0, 1)

This query will return a 1 in Upper if [field] is upper case, etc.

Make it a Totals query and Sum the three fields to get a count of each.

 

[Michel Walsh]

As example, with


fieldOfName
hello
Hello
hello
HEllo
Hello
hellO
hello




the result is


fieldOfName theCount
hello 3
hellO 1
Hello 2
HEllo 1