I need to compare 2 text values in MS Access. My problem is this;
Access considers numerics to precede alphas in an ascending alphabetical sort. So, for example, the values 1XX, AXX, 2XX, BXX would be sorted,
1XX
2XX
AXX
BXX
I have a request to compare these values in a way in which alphas would precede numerics so that the list would appear like this:
AXX
BXX
1XX
2 XX
Is there an easy way to do this, and barring easy what are the more difficult solutions?
Thanks in advance!
The reason that Access sorts that way is due to the fact that the ASCII representations of alphanumeric characters sort that way when you consider them as integers. There’s not going to be a very easy solution, but you might be able to fake it by coming up with a script that generates an integer value from each string so that the integers sort that way, and sort by that. Do you follow?
Yeah, use a UNION statement with two selects.
I have a database with a table, SortThis, with two columns, “dummyID” (automumber) and Entry (text). Entry contains the following data: {aaa, bbb, ccc, 111, 222, 333}.
The following query will do something very similar to the behavior you want:
SELECT SortThis.dummyID, SortThis.Entry
FROM SortThis
WHERE (((SortThis.Entry) Like "#*"))
ORDER BY SortThis.Entry
UNION
SELECT SortThis.dummyID, SortThis.Entry
FROM SortThis
WHERE (((SortThis.Entry) Like "[a-z]*"))
ORDER BY SortThis.Entry;
Will that sort a1, aa, a2, ab to the proper order?
I now have the following data {aaa, bbb, ccc, 111, 222, 333, abc, 123, 1a1, 1aa, a1a, a11}
The above query yields
dummyID Entry
4 111
8 123
10 1a1
11 1aa
5 222
6 333
13 a11
12 a1a
1 aaa
7 abc
2 bbb
3 ccc
Shouldn’t aaa come before 111 in the OP’s logic?
erislover, that wasn’t the sort that the OP asked for, and the order you specify union clauses doesn’t affect the final sort order. However, this variant worked:
SELECT SortThis.dummyID, 'a' as flag, SortThis.Entry
FROM SortThis
WHERE (((SortThis.Entry) Like "[a-z]*"))
UNION
SELECT SortThis.dummyID, 'b' as flag, SortThis.Entry
FROM SortThis
WHERE (((SortThis.Entry) Like "#*"))
ORDER BY flag, SortThis.Entry;
results
dummyID flag Entry
13 a a11
12 a a1a
1 a aaa
7 a abc
2 a bbb
3 a ccc
4 b 111
8 b 123
10 b 1a1
11 b 1aa
5 b 222
6 b 333
The union query is really spurious in this case, since both halves have the same base table… you could derive flag as iif((SortThis.Entry) Like “[a-z]*”, ‘a’, ‘b’) instead or something like that I believe.
(checks)
Ahh, yes, without the union query you cannot sort by a name from the field list. However, you can do this:
SELECT SortThis.dummyID, SortThis.Entry
FROM SortThis
ORDER BY IIf(([SortThis].[Entry]) Like "[a-z]*",'a','b'), SortThis.Entry;
which has the same results without a flag field in the result set.
Yes, and :smack:
I can’t get the UNION to not sort when it is assembled in the way I’m thinking. I even went so far as to make two separate queries which had the suggested behavior (grab only those that begin with a number in one, grab only those that begin with a letter in the other) and they look right. But when they are unioned, they are sorted again in some manner.
So, nevermind. This seems like it should give the desired behavior, but it isn’t.
Good, chrisk. I thought of the “flag” style, too, but for some reason I was convinced they would UNION in the way I envisioned. But I see why. You are not supposed to have an “ORDER BY” clause in either table created by the select statements (though you can order the final table). My bad. I like the boolean sort, though, really good idea.
You guys are gonna kill me - I should have been more clear.
The sort needs to take into account all charachters in each string regardless of the length of the strings (the lenghts of the strings to be comparded may differ).
Thanks for the help and I appologize for not being clear :smack:
That’s tougher… I’m not gonna kill you, but I’m not gonna help you out much more hehe.
The only suggestion I have is this… derive a VBA function or some such that can convert the strings into something that access will know how to sort the way you want to. You probably will only be able to get only about the first 6 characters or so (4 billion log 36) into a long integer field, so your best bet will be converting the string into a different kind of field… shunt alphabetic characters into the punctuation range and the numbers up so they’re further on in the order. (An ASCII codes chart will be invaluable for this.)
Then sort by this new ordering field. The liability is that you need to make sure to update the field for every change you make in your table, but it’s the only workaround I can see for teaching access an entirely new kind of lexicographic ordering.
Ask if you have any questions about this answer.
Now THAT has some interesting potential.
Thanks all for the help! I had a feeling this was going to be a tough nut to crack. 
Okay, I started to obsess a little over this problem, and it was a good stretch of my programming muscles, so I wrote a little code.
Since you didn’t say what should happen with spaces, punctuation, and any other characters in the field, I assumed that letters go first of all, and otherwise things are the same… things that would be before digits are still before digits, things that are usually after go after.
Public Function deriveSortField(e As String) As String
Dim tempStr As String, l As Integer, k As Integer
Dim c1 As String, a As Integer, c2 As String
l = Len(e)
tempStr = Space(l)
For k = 1 To l
c1 = Mid(e, k, 1)
a = Asc(UCase(c1))
If a >= 65 And a <= 90 Then ' letter
c2 = Chr(a - 33)
ElseIf a >= 32 And a <= 57 Then ' number or early-punc
c2 = Chr(a + 26)
ElseIf a > 90 And a <= 127 Then
c2 = c1
Else
c2 = Chr(127)
End If
Mid(tempStr, k, 1) = c2
Next
deriveSortField = tempStr
End Function
Public Sub assignSortFields()
Dim conn As Connection, rst As New Recordset
Set conn = CurrentProject.Connection
Dim e As String, s As String
rst.Open "sortThis", conn, adOpenKeyset, adLockPessimistic
Do While Not rst.EOF
e = rst("entry")
s = deriveSortField(e)
rst("sortField") = s
rst.Update
rst.MoveNext
Loop
MsgBox "Done."
End Sub
assignSortFields is the macro you need to call to update everything in the table… deriveSortField is the function that actually comes up with the new sort values. Hope this helps!!
results:
dummyID Entry sortField
1 aaa
7 abc !"
12 a1a K
13 a11 KK
2 bbb !!!
3 ccc """
11 1aa K
10 1a1 K K
4 111 KKK
8 123 KLM
5 222 LLL
6 333 MMM