case-sensitive sorting (hex problem)

[Dave_epic]

Hi!

I got this used defined function off the microsoft online access (see below). The purpose is to change an ascii string into hex characters so they can be sorted case-sensitively.


********************************************************

Function StrToHex (S As Variant) As Variant
'
' Converts a string to a series of hexadecimal digits.
' For example, StrToHex(Chr(9) & "A~") returns 09417E.
'
Dim Temp As String, I As Integer
If VarType(S) <> 8 Then
StrToHex = S
Else
Temp = ""
For I = 1 To Len(S)
Temp = Temp & Format(Hex(Asc(Mid(S, I, 1))), "00")
Next I
StrToHex = Temp
End If
End Function


******************************************************


where the sql to call it is::

Expr1: StrToHex([SortField]).




And it works to an extent but....

In the results it seems to class any hex character with an 'A' in it as 00. So for example ascii 'J' which is '4A' in hex is displayed as 00 and therefore comes at the beginning of the list in the query results. However hex characters with 'B' or 'C' in them for example such as 'K' are expressed correctly as hex numbers and come in the correct place. Any idea why this is and how to fix it?

query results as below:

Field1 Expr1
: 00
* 00
Z 00
j 00
z 00
J 00
A 41
B 42
C 43
D 44
E 45
F 46
K 4B
L 4C
M 4D


http://office.microsoft.com/en-us/access/HA100627601033.aspx



Thanks!!!

 

[jdraw]

Hi!

I got this used defined function off the microsoft online access (see below). The purpose is to change an ascii string into hex characters so they can be sorted case-sensitively.


********************************************************

Function StrToHex (S As Variant) As Variant
'
' Converts a string to a series of hexadecimal digits.
' For example, StrToHex(Chr(9) & "A~") returns 09417E.
'
Dim Temp As String, I As Integer
If VarType(S) <> 8 Then
StrToHex = S
Else
Temp = ""
For I = 1 To Len(S)
Temp = Temp & Format(Hex(Asc(Mid(S, I, 1))), "00")
Next I
StrToHex = Temp
End If
End Function


******************************************************


where the sql to call it is::

Expr1: StrToHex([SortField]).




And it works to an extent but....

In the results it seems to class any hex character with an 'A' in it as 00. So for example ascii 'J' which is '4A' in hex is displayed as 00 and therefore comes at the beginning of the list in the query results. However hex characters with 'B' or 'C' in them for example such as 'K' are expressed correctly as hex numbers and come in the correct place. Any idea why this is and how to fix it?

query results as below:

Field1 Expr1
: 00
* 00
Z 00
j 00
z 00
J 00
A 41
B 42
C 43
D 44
E 45
F 46
K 4B
L 4C
M 4D


http://office.microsoft.com/en-us/access/HA100627601033.aspx



Thanks!!!

Have you tried doing a case sensitive sort in Access.
Here is a tip from fmsinc I got via google.

Tip 3: Make sure Option Compare is set correctly

Option Compare specifies how string comparisons are evaluated in the module such as case sensitive vs. insensitive comparisons (e.g. should "A" = "a" be True or False?).

By default, Access/VBA uses:

Option Compare Database

This is a case insensitive comparison and respects the sort order of the database. In VB, which doesn't have the Database option, it's the same as the Text option:

Option Compare Text

That means, "A" = "a", which are both less than "B".

For exact (case sensitive) comparisons, so "A" is not the same as "a", use:

Option Compare Binary

If you are debugging code and confused because you can't understand seemingly valid text comparison failing when it works in another module, be sure to check the module's Option Compare setting. For instance, if strValue below is "YES", the evaluation below differs based on the Option Compare setting:

If strValue = "Yes" Then

In general, you should use the default Option Compare Database for your Access VBA code. If you need to make a case insensitive comparison, use the StrComp function with the vbBinaryCompare option:

StrComp(string1, string2, vbBinaryCompare)

That way you can move the code into any module and always have case sensitive comparisons without worrying about the Option Compare setting.

Good luck.