VBA compare and sort strings with quirky characters
- by Smandoli
I am comparing text values from two DAO recordsets in MS Access. I sort on the text field, then go through both recordsets comparing the values from each. The sets are substantially different and while they're mostly alpha-numeric, spaces and symbols like hyphens and periods are very common.
My program depends on predictable sorting and fool-proof comparing. But unfortunately, the sort will rank two values differently than the comparison function.
StrComp is the obvious first choice:
varResult = StrComp(Val_1, Val_2)
RFA-300 14.9044
RFA300 14-2044
But for the two pairs above, StrComp returns a different value than one would expect based on the sort. Including vbTextCompare or vbBinaryCompare affects StrComp's result, but not so as to solve the problem.
Note the values must always be compared as strings. Of course I make sure that "14-2044" and "14.9044" aren't evaluated as -2030 and ~15. That's not the cause of my problem.
I learned API-based functions are more reliable for quirky texts, so I tried these:
varResult = CompareString(LOCALE_SYSTEM_DEFAULT, _
SORT_STRINGSORT, strVal_2, -1, strVal_1, -1)
varResult = CompareString(LOCALE_SYSTEM_DEFAULT, _
NORM_IGNOREWIDTH, strVal_2, -1, strVal_1, -1)
The first one returns the opposite of StrComp. The second one returns the same as StrComp. But neither yields a result that is consistent with the sort order. (NORM_IGNOREWIDTH is probably not relevant, but I needed a place-holder substitute and it looked as good as any.)
UPDATE: This is a complete rewrite of the original post, deleting all the info about why I really need this -- just take my word for it and enjoy the brevity.
