UK Postcode Sort

[Colin]

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.

 

[Peter R. Fletcher]

UK Postcodes are such fun! The easiest approach is probably to write
little functions which return the two separate parts of the postcode
(one as a string and the other as an integer) and use their return
values in sorted hidden fields in your queries I don't think that
there is "an expression I can type into my query"! It is the fact that
the alpha component can be either one or two characters that really
makes "single line solutions" difficult to come up with.

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.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Colin]

Thanks Peter for your time in replying.
There is an article in the microsoft knowledge base no.
Q209632. It shows how to sort alphanumeric strings, but
is the other way round to the UK postcodes?? Ther must be
a way??
I'll plod on.Thanks, Colin.

 

[Peter R. Fletcher]

I looked at the KB article - it won't help you because it relies on
there being one and only one alpha character. As I said, it is the
fact that a postcode may have one or two alpha characters that causes
the problem. What's wrong with the VBA approach I suggested?

Thanks Peter for your time in replying.
There is an article in the microsoft knowledge base no.
Q209632. It shows how to sort alphanumeric strings, but
is the other way round to the UK postcodes?? Ther must be
a way??
I'll plod on.Thanks, Colin.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Andrew L.]

Colin

I think the last 6 characters of UK postcodes are always
NUMBER-SPACE-NUMBER-LETTER-LETTER. The following query works for whole postcodes (with a
space between the two elements).

Create a select query. In the first column enter this expression:-

Expr1: IIf(IsNumeric(Mid([POSTCODE],InStr([POSTCODE],"
")-2,1)),[POSTCODE],Left([POSTCODE],InStr([POSTCODE]," ")-2) & "0" & Right([POSTCODE],5))

Sort this column Ascending, but don't show it. Insert your POSTCODE field as the second
column, and show it.

HTH
Andrew L.

 

[Colin]

Thanks Andrew.
Your information is excellent.
The only issue is coming up with invalid procedure call.
I've inputted as you have written several times.

The postcodes that I need to sort are only the first part
of the UK postcodes ie. AB1, AB10, NG5, L8, NE13.
Andrew if you could sort this for me, i would be very
grateful.
Many Thanks for your time.

Colin.

-----Original Message-----
Colin

I think the last 6 characters of UK postcodes are always
NUMBER-SPACE-NUMBER-LETTER-LETTER. The following query

works for whole postcodes (with a

space between the two elements).

Create a select query. In the first column enter this expression:-

Expr1: IIf(IsNumeric(Mid([POSTCODE],InStr([POSTCODE],"
")-2,1)),[POSTCODE],Left([POSTCODE],InStr

([POSTCODE]," ")-2) & "0" & Right([POSTCODE],5))

Sort this column Ascending, but don't show it. Insert

your POSTCODE field as the second

 

[Andrew L.]

Colin, this should do it:-

Expr1:
IIf(IsNumeric(Mid([POSTCODE],Len([POSTCODE])-1,1)),[POSTCODE],Left([POSTCODE],Len([POSTCODE])-1) &
"0" & Right([POSTCODE],1))

Andrew L.

Thanks Andrew.
Your information is excellent.
The only issue is coming up with invalid procedure call.
I've inputted as you have written several times.

The postcodes that I need to sort are only the first part
of the UK postcodes ie. AB1, AB10, NG5, L8, NE13.
Andrew if you could sort this for me, i would be very
grateful.
Many Thanks for your time.

Colin.

-----Original Message-----
Colin

I think the last 6 characters of UK postcodes are always
NUMBER-SPACE-NUMBER-LETTER-LETTER. The following query

works for whole postcodes (with a

space between the two elements).

Create a select query. In the first column enter this expression:-

Expr1: IIf(IsNumeric(Mid([POSTCODE],InStr([POSTCODE],"
")-2,1)),[POSTCODE],Left([POSTCODE],InStr

([POSTCODE]," ")-2) & "0" & Right([POSTCODE],5))

Sort this column Ascending, but don't show it. Insert

your POSTCODE field as the second

column, and show it.

HTH
Andrew L.

"Peter R. Fletcher" <pfletch(at)fletchers(hyphen)uk.com> wrote in message


.

 

[Peter R. Fletcher]

Not correct, I am afraid. There are some, mainly but not (I believe)
entirely in London, where the first block has an alpha character
following the number. I don't have a real example immediately to hand,
but a made up example would look like: EC1S 2PP. I still think that
VBA is the way to go, here.

Colin

I think the last 6 characters of UK postcodes are always
NUMBER-SPACE-NUMBER-LETTER-LETTER. The following query works for whole postcodes (with a
space between the two elements).

Create a select query. In the first column enter this expression:-

Expr1: IIf(IsNumeric(Mid([POSTCODE],InStr([POSTCODE],"
")-2,1)),[POSTCODE],Left([POSTCODE],InStr([POSTCODE]," ")-2) & "0" & Right([POSTCODE],5))

Sort this column Ascending, but don't show it. Insert your POSTCODE field as the second
column, and show it.

HTH
Andrew L.

I looked at the KB article - it won't help you because it relies on
there being one and only one alpha character. As I said, it is the
fact that a postcode may have one or two alpha characters that causes
the problem. What's wrong with the VBA approach I suggested?



Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Andrew L.]

Peter

You are absolutely right. And I'm sure VBA is the way to go. However the OP may not have enough
time to explore this option. The solution I offered was a quick & easy QBE alternative. It has the
virtue of actually working (I tested it before posting). A slightly longer version will work even
for London postcodes.

Andrew L.

Not correct, I am afraid. There are some, mainly but not (I believe)
entirely in London, where the first block has an alpha character
following the number. I don't have a real example immediately to hand,
but a made up example would look like: EC1S 2PP. I still think that
VBA is the way to go, here.

Colin

I think the last 6 characters of UK postcodes are always
NUMBER-SPACE-NUMBER-LETTER-LETTER. The following query works for whole postcodes (with a
space between the two elements).

Create a select query. In the first column enter this expression:-

Expr1: IIf(IsNumeric(Mid([POSTCODE],InStr([POSTCODE],"
")-2,1)),[POSTCODE],Left([POSTCODE],InStr([POSTCODE]," ")-2) & "0" & Right([POSTCODE],5))

Sort this column Ascending, but don't show it. Insert your POSTCODE field as the second
column, and show it.

HTH
Andrew L.

I looked at the KB article - it won't help you because it relies on
there being one and only one alpha character. As I said, it is the
fact that a postcode may have one or two alpha characters that causes
the problem. What's wrong with the VBA approach I suggested?

On Wed, 5 May 2004 11:54:56 -0700, "Colin"

Thanks Peter for your time in replying.
There is an article in the microsoft knowledge base no.
Q209632. It shows how to sort alphanumeric strings, but
is the other way round to the UK postcodes?? Ther must be
a way??
I'll plod on.Thanks, Colin.
-----Original Message-----
UK Postcodes are such fun! The easiest approach is
probably to write
little functions which return the two separate parts of
the postcode
(one as a string and the other as an integer) and use
their return
values in sorted hidden fields in your queries I don't
think that
there is "an expression I can type into my query"! It is
the fact that
the alpha component can be either one or two characters
that really
makes "single line solutions" difficult to come up with.

On Wed, 5 May 2004 05:38:58 -0700, "Colin"

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.

Please respond to the Newsgroup, so that others may
benefit from the exchange.
Peter R. Fletcher
.


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Peter R. Fletcher]

You mean something like:

IIF(IsNumeric(Mid$([POSTCODE],2,1)),IIF(IsNumeric(Mid$([POSTCODE],3,1)),[POSTCODE],Mid$([POSTCODE],1,1)
& "0" &
Mid$([POSTCODE],2)),IIF(IsNumeric(Mid$([POSTCODE],4,1)),[POSTCODE],Mid$([POSTCODE],1,2)
& "0" & Mid$([POSTCODE],3))

I think it works, but I hate it!

The VBA code took me a bit less than an hour to write and is a lot
easier to understand and use!

Peter

You are absolutely right. And I'm sure VBA is the way to go. However the OP may not have enough
time to explore this option. The solution I offered was a quick & easy QBE alternative. It has the
virtue of actually working (I tested it before posting). A slightly longer version will work even
for London postcodes.

Andrew L.

Not correct, I am afraid. There are some, mainly but not (I believe)
entirely in London, where the first block has an alpha character
following the number. I don't have a real example immediately to hand,
but a made up example would look like: EC1S 2PP. I still think that
VBA is the way to go, here.

Colin

I think the last 6 characters of UK postcodes are always
NUMBER-SPACE-NUMBER-LETTER-LETTER. The following query works for whole postcodes (with a
space between the two elements).

Create a select query. In the first column enter this expression:-

Expr1: IIf(IsNumeric(Mid([POSTCODE],InStr([POSTCODE],"
")-2,1)),[POSTCODE],Left([POSTCODE],InStr([POSTCODE]," ")-2) & "0" & Right([POSTCODE],5))

Sort this column Ascending, but don't show it. Insert your POSTCODE field as the second
column, and show it.

HTH
Andrew L.

"Peter R. Fletcher" <pfletch(at)fletchers(hyphen)uk.com> wrote in message
I looked at the KB article - it won't help you because it relies on
there being one and only one alpha character. As I said, it is the
fact that a postcode may have one or two alpha characters that causes
the problem. What's wrong with the VBA approach I suggested?

On Wed, 5 May 2004 11:54:56 -0700, "Colin"

Thanks Peter for your time in replying.
There is an article in the microsoft knowledge base no.
Q209632. It shows how to sort alphanumeric strings, but
is the other way round to the UK postcodes?? Ther must be
a way??
I'll plod on.Thanks, Colin.
-----Original Message-----
UK Postcodes are such fun! The easiest approach is
probably to write
little functions which return the two separate parts of
the postcode
(one as a string and the other as an integer) and use
their return
values in sorted hidden fields in your queries I don't
think that
there is "an expression I can type into my query"! It is
the fact that
the alpha component can be either one or two characters
that really
makes "single line solutions" difficult to come up with.

On Wed, 5 May 2004 05:38:58 -0700, "Colin"

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.

Please respond to the Newsgroup, so that others may
benefit from the exchange.
Peter R. Fletcher
.


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Peter R. Fletcher]

On Wed, 5 May 2004 11:54:56 -0700, "Colin"

<SNIP>


Here is the VBA code to include in a General module and call from your
Query. As long as the first part of the Postcode is valid, the two
functions will return (respectively) its alpha and integer components.
Most invalid codes will return the Null string and -1 (again
respectively). I think that I have handled all possible bad arguments.
I have gone for clarity rather than speed, but the Functions should
not be too inefficient. They will almost certainly be faster than the
inline code I did eventually come up with.

Function AlphaPart(PostCode As Variant) As String
' Returns the alpha part of the first "block" of a UK Postcode
' a null string is the error return

Dim strTemp As String
Dim strReturn As String
Dim blDone As Boolean

AlphaPart = vbNullString
blDone = False

If IsNull(PostCode) Then Exit Function
If VarType(PostCode) <> vbString Then Exit Function
If Len(PostCode) < 2 Then Exit Function

' Deal with likely bad arguments

strTemp = UCase(PostCode) ' just in case it wasn't

strReturn = Mid$(strTemp, 1, 1)

Select Case strReturn
Case "A" To "Z"
' that's OK, continue
Case Else
Exit Function ' not a valid postcode
End Select

Select Case Mid$(strTemp, 2, 1)
Case "A" To "Z"
strReturn = strReturn & Mid$(strTemp, 2, 1)
Case "0" To "9"
blDone = True
Case Else
Exit Function ' not a valid postcode
End Select

If blDone Then
AlphaPart = strReturn
Else
Select Case Mid$(strTemp, 3, 1)
Case "0" To "9"
AlphaPart = strReturn
Case Else
' not a valid postcode
End Select
End If

End Function


Function NumPart(PostCode As Variant) As Integer
' Returns the numeric part of the first "block" of a UK Postcode as an
integer
' -1 is the error return

Dim strTemp As String
Dim intReturn As Integer
Dim blDone As Boolean

NumPart = -1
blDone = False

If IsNull(PostCode) Then Exit Function
If VarType(PostCode) <> vbString Then Exit Function
If Len(PostCode) < 2 Then Exit Function

strTemp = UCase(PostCode)

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
Exit Function ' invalid postcode
Case "A" To "Z"
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
' start of the numeric part
Case "A" To "Z"
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

' now we should be at the start of the numeric part

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
intReturn = CInt(Mid$(strTemp, 1, 1))
strTemp = Mid$(strTemp, 2)
Case Else
Exit Function ' invalid postcode
End Select

' is there another digit?

Select Case Mid$(strTemp, 1, 1)
Case "0" To "9"
intReturn = intReturn * 10 + CInt(Mid$(strTemp, 1, 1))
Case Else
blDone = True
End Select

If blDone Then
NumPart = intReturn
Else
Select Case Mid$(strTemp, 2, 1)
Case "0" To "9" ' there shouldn't be another!
' not a valid postcode
Case Else
NumPart = intReturn
End Select
End If

End Function

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher