Case Sensitve and Unique Values

[PZ]

I am attempting to create a query (without any luck) that
returns unique values where the same name in lower case,
mixed case or upper case is considered unique.

Also names may be mixed case with case of individual
characters different for the same name in different
records.

For Example:

CORPoration
Corporation
corporation
CORPORATION
corpoRatioN

All of these need to returned as unique values.

Any help would be appreciated.

Thanks

 

[Douglas J. Steele]

It's difficult in Access, since it's designed to be case insensitive.

One thing you can do is write a function that converts each character in
your string to its ASCII representation, and use that.

Something like the following untested air-code might work:

Function ConvertToAscii(StringToConvert As String) As String

Dim intLoop As Integer
Dim strOutput As String


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

ConvertToAscii = strOutput

End Function

Here's sample output of using it:

?ConvertToAscii("Corporation")
067111114112111114097116105111110
?ConvertToAscii("corporation")
099111114112111114097116105111110
?ConvertToAscii("CORPoration")
067079082080111114097116105111110

As you can see, all three ASCII strings are different.