how do I group by with case sensitivity on

M

Matthew567

I have a database with many thousands of words each in a separate cell. As an
example the words "Each" and "each" appear within it. When I use the GROUP BY
clause it treats these as the same. I would like separate entries in the
results table for each of the two words given above. Many thanks.
 
J

Jerry Whittle

If the first character of the string is good enough, you could do something
with the Asc() function.

Debug.Print ASC("Each") returns 69

Debug.Print ASC("each") returns 101

Debug.Print ASC("e") also returns 101

Upper case characters return a lower number than the lower case equivelant.
 
M

Matthew567

Thanks, Jerry -- in practice this works as follows:

SELECT table.words, Count(table.words) AS CountOfwords
FROM table
GROUP BY table.words, (Asc([words]));

though it would also be useful to do with complete strings.
 
M

Matthew567

Karl,

thanks also. This seems a more precise way of formulating asc() for this
purpose.
 
G

Gary Walter

PMFBI

Public Function fGetAsc(pString As Variant) As String
On Error GoTo Err_fGetAsc
Dim I As Integer
If Len(Trim(pString & "")) > 0 Then
For I = 1 To Len(pString)
fGetAsc = fGetAsc & Asc(Mid(pString, i)) & "-"
Next
fGetAsc = Left(fGetAsc, Len(fGetAsc) - 1)
Else
fGetAsc = "Null or ZLS"
End If
Exit_fGetAsc:
Exit Function

Err_fGetAsc:
MsgBox Err.Description
Resume Exit_fGetAsc
End Function

Public Function fGetAscNoHyph(pString As Variant) As String
On Error GoTo Err_fGetAscNoHyph
Dim i As Integer
If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
fGetAscNoHyph = fGetAscNoHyph & Asc(Mid(pString, i))
Next
Else
fGetAscNoHyph = "Null or ZLS"
End If
Exit_fGetAscNoHyph:
Exit Function

Err_fGetAscNoHyph:
MsgBox Err.Description
Resume Exit_fGetAscNoHyph
End Function


test in Immediate Window

?fgetascnohyph("easy")
10197115121
?fgetasc("easy")
101-97-115-121


Thanks, Jerry -- in practice this works as follows:

SELECT table.words, Count(table.words) AS CountOfwords
FROM table
GROUP BY table.words, (Asc([words]));

though it would also be useful to do with complete strings.

Jerry Whittle said:
If the first character of the string is good enough, you could do
something
with the Asc() function.

Debug.Print ASC("Each") returns 69

Debug.Print ASC("each") returns 101

Debug.Print ASC("e") also returns 101

Upper case characters return a lower number than the lower case
equivelant.
Click to expand...
Click to expand...
 
Top