Sort Query

  • Thread starter Thread starter COL
  • Start date Start date
C

COL

Hi, i would be grateful for some help. I have around 400
postcodes that i would like to sort.The issue is that it
sorts as follows BB1, BB11, BB12, BB2, BB23 etc.
I would like it sorted numerical, as well as alphabetical
ie. BB1, BB2, BB11, BB12, BB23. The UK postcodes start
with either 1 or 2 letters (alphabetical) 'b' or 'bb' and
finish with 1 or 2 numbers ie '1' or '12' with no leading
zero's.
Has anyone an expression i can type into my query. Thanks
in advance. Colin.
 
Amazing, Thanks Steve.

-----Original Message-----

Colin,

Maybe this will help you get started.

This is what I found out about the UK postcodes:

'UK Postcodes must conform to the following rules:
'
'The total length must be 6,7, or 8 characters, a gap
Click to expand...
(space character) must be included
'The inward code, the part to the right of the gap, must always be 3 characters
'The first character of the inward code must be numeric
'The second and third characters of the inward code must be alpha

'The outward code, the part to the left of the gap, can be 2,3, or 4 characters
'The first character of the outward code must be alpha

'There are over 1.7 million UK postcodes, and that number grows daily.


Create a standard module and paste in the following code:

'------------ Code start
'This code cannot guarantee that a particular code is valid,
' it assumes that the format for the Outward part is correct.
'

Public Function fPostCodeSort(PostCode As String) As String
Dim i As Integer
Dim PC_Len As Integer
Dim Zip As String
Dim strNumPart As String ' range from 1 to 99
Dim strAlphaPart As String
Dim OutwardCode As String

Zip = Trim(UCase(PostCode))
PC_Len = Len(Zip)
If PC_Len > 4 Then
i = InStr(1, Zip, " ")
OutwardCode = Left(Zip, i - 1)
Else
OutwardCode = Zip
End If

If PC_Len = 4 Then
fPostCodeSort = OutwardCode
Else
'seperate the letters from the numbers
'1 or 2 letters and 1 or 2 numbers
For i = 1 To Len(OutwardCode)
If IsNumeric(Mid(OutwardCode, i, 1)) Then
strNumPart = strNumPart & Mid (OutwardCode, i, 1)
Else
strAlphaPart = strAlphaPart & Mid (OutwardCode, i, 1)
End If
Next
' if number is < 10, pad with leading zero
If Len(strNumPart) = 1 Then
strNumPart = "0" & strNumPart
End If
fPostCodeSort = strAlphaPart & strNumPart
End If

End Function
'------------ Code end

Now, in your query, add a new column:

Expr1: fpostcodesort([UK_postcode])

Change [UK_postcode] to what you named the field that contains the postcode

In the query, set the Sort to Ascending for Expr1
Click to expand...
column, and remove it (if it is there) for the postcode
column.
 
Back
Top