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.

 

[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.

 

[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