Sort Alphanumeric post codes

[efandango]

I have a query that feeds postcodes to a combo box. problem is that it sorts
them in strict alphanumeric format; like this example:

E1
E10
E11
E12
E2
E3

What i want is:

E1
E2
E3
E10
E11


My query uses another table to make sure only valid (exisitng in the
database) postcodes are pulled from the table, and it seems to be this
connection that makes things difficult to sort in another way.



My SQL:

SELECT tbl_PostCodes.Postcode, tbl_PostCodes.Postcode_ID
FROM tbl_PostCodes INNER JOIN tbl_Points ON tbl_PostCodes.Postcode =
tbl_Points.Run_Point_Postcode
GROUP BY tbl_PostCodes.Postcode, tbl_PostCodes.Postcode_ID;

 

[Ken Sheridan]

Its not the join which is the problem it’s the fact that the numeric part of
each postcode is part of the string expression, so is sorted as such, e.g.
"10" sorts before "2". Add the following function a standard module:

Public Function PostCodeSorted(varCode As Variant) As Variant

Dim strAlpha As String
Dim strChr As String
Dim strNum As String
Dim n As Integer

If Not IsNull(varCode) Then
For n = 1 To Len(varCode)
strChr = Mid(varCode, n, 1)
If IsNumeric(strChr) Then
strAlpha = Left(varCode, n - 1)
strNum = Format(Mid(varCode, n), "0000")
Exit For
End If
Next n

PostCodeSorted = strAlpha & strNum
End If

End Function

The function returns the postcode with the numeric element formatted to four
digits with leading zeros as necessary, and therefore capable of being sorted
numerically. Call the function in the query's ORDER BY clause:

SELECT tbl_PostCodes.Postcode, tbl_PostCodes.Postcode_ID
FROM tbl_PostCodes INNER JOIN tbl_Points
ON tbl_PostCodes.Postcode = tbl_Points.Run_Point_Postcode
GROUP BY tbl_PostCodes.Postcode, tbl_PostCodes.Postcode_ID
ORDER BY PostCodeSorted(tbl_PostCodes.Postcode);

Ken Sheridan
Stafford, England

 

[efandango]

Ken,

Thanks for your coding suggestion. I will give it a try.

regards

Eric