Re: UK Postcode Sort

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andrew L. (andrew_at_aelaycock.fsnet.co.uk)
Date: 05/06/04 

Date: Thu, 6 May 2004 06:26:17 +0100

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.
"Colin" <anonymous@discussions.microsoft.com> wrote in message
news:8d2401c432f2$e9059c70$a401280a@phx.gbl...
> 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
> >news:oiei90hqtb6f19l57nqh64di5ofk6t51dd@4ax.com...
> >> 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"
> >> <anonymous@discussions.microsoft.com> wrote:
> >>
> >> >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"
> >> >><anonymous@discussions.microsoft.com> wrote:
> >> >>
> >> >>>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
> >
> >.
> >

Relevant Pages

  • Re: Slide film processed as negative!
    ... Andrew Haley wrote: ... >> Colin D wrote: ... >>> on getting back to her home in Hong Kong, handed in her Fuji slide films ... > it through the same cross-processing at the same lab. ...
    (rec.photo.equipment.35mm)
  • Re: FC4:ToUcam Pro II webcam (and astronomy.)
    ... Andrew> Hi All, Has anyone here managed to get the Phillips ToUcam ... Andrew> webcam working under Fedora Core 4? ... Colin> if I just relink, ... So here is my patch against a 2.28 source tree: ...
    (Fedora)
  • Re: How to change XPs User Account to 2000-style
    ... Thanks so much Colin for your help. ... Andrew ... And I guess they want to use the same cute interface for both so ... > Power Users show up. ...
    (microsoft.public.windowsxp.security_admin)
  • Losing friends/Luck of the draw-- Long
    ... We used to work together at a movie theater in Georgetown, Washington, D.C., ... The employee's handbook Andrew wrote for the theater contained this ... He left them there as a sort of a shrine. ... Sad and horrifying... ...
    (alt.support.stop-smoking)
  • Re: Losing friends/Luck of the draw-- Long
    ... always thought of Andrew on my birthday. ... We used to work together at a movie theater in Georgetown, Washington, D.C., ... He left them there as a sort of a shrine. ... Sad and horrifying... ...
    (alt.support.stop-smoking)