In VBA, why is the Hex code of "Z" all zeros? [duplicate]

Question

I am baffled by the following results in the immediate execution window of VBE

print AscW("Z")
 90 
print Hex(AscW("Z"))
5A
print Format(Hex(AscW("Z")),"0000")
0000
print Format(&H5A,"0000")
0090
print Format(Hex(AscW("A")),"0000")
0041

It looks like Hex function returns a string, since there is no space before the returned "5A". Perhaps Format does not work with strings?

However, "A" behaves as expected, as shown in the last line above.

What's going on? Can anyone help me? I am using this function to emulate a case-sensitive OrderBy in Access, and this phenomenon is putting the Z strings in front of A strings and all other strings.

Answer 1

I found the problem. hex of "Z" (i.e. AscW("Z")) is "5A", which contains "A", and is regarded as 0 by Format. The reason "A" works is because its hex (AscW("A")) is "62", which is a numeric string, and Format treats it like a number.

The proper way to do it is not to use Format, but thus: right("0000" & Hex(AscW("Z")),4)

Thanks for reading.