A case sensitive version of "SELECT DISTINCT" for Jet SQL

[Guest]

Hi,

It appears the "Select Distinct" groups on a case insensitive manner. Is
there a way to make it case sensitive?

 

[Jason Lepack]

Your data model must be very strange if you really want to see two
different groupings for "One" and "one"....

Now you've really got me interested. Why do you want to do that?

I miss the old Oracle days when all data was in CAPS.

Cheers,
Jason Lepack

 

[Douglas J. Steele]

I agree with Jason that this is an unusual requirement!

One approach is to write a function that takes each string and converts it
to a series of Ascii codes:

Function ConvertToASCII(StringIn As String) As String

Dim intLoop As Integer
Dim strOut As String

strOutput = vbNullString
For intLoop = 1 To Len(StringIn)
strOut = strOut & Format$(Asc(Mid$(StringIn, intLoop, 1)), "000")
Next intLoop

ConvertToASCII = strOut

End Function

?ConvertToASCII("One")
079110101
?ConvertToASCII("one")
111110101

Use that function to create an additional field in your query that can be
used for grouping.