How can I use DISTINCT with Case Sensitivity?

[Guest]

Hi,

I have read through the posts here and go through articles from MS, but
their solutions/workaround seems not satisfying.

Workarounds they suggested:
- To use StrComp() or ASC() functions
- To use other database or odbc driver that is case-sensitivity
- To use BINARY field

Currently I am using SELECT DISTINCT UNIX_LOGIN, ASC(USERNAME) ...
to avoid case sensitivity in my distinct. But it only works with the first
letter of UNIX_LOGIN field.

Anyone has better ideas to cope with this problem? I need alternatives since
I have to compare their performance.

Thanks a lot,
Pongthai

 

[Gary Walter]

I have read through the posts here and go through articles from MS, but
their solutions/workaround seems not satisfying.

Workarounds they suggested:
- To use StrComp() or ASC() functions
- To use other database or odbc driver that is case-sensitivity
- To use BINARY field

Currently I am using SELECT DISTINCT UNIX_LOGIN, ASC(USERNAME) ...
to avoid case sensitivity in my distinct. But it only works with the first
letter of UNIX_LOGIN field.

Anyone has better ideas to cope with this problem? I need alternatives
since
I have to compare their performance.

One slow option would be to save the following
function in a code module:

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

test in Immediate Window:

UNIX_LOGIN = "abc"
?fGetAsc(UNIX_LOGIN)
97-98-99
UNIX_LOGIN = "Abc"
?fGetAsc(UNIX_LOGIN)
65-98-99