Postcode Sorting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

oliaccount - 23 Aug 2007 11:25 GMT

Hi again
(The day I am skillful enough to actually answer some of these questions!)
I've been trying to sort a report by postcode, however access sorts them like
this: LE1, LE11, LE13, LE2, instead of like this: LE1, LE2, LE11, LE13, and
so on.

I've looked at various solutions posted around the internet, but they either
use code that goes over my head, or they don't explain the solution in enough
detail for me to follow. If anyone could guide me though the proccess of this
kind of sorting, I would really appreicate it. Thanks!

Reply to this Message

Jeff Boyce - 23 Aug 2007 12:32 GMT

Based on your example, you appear to want to sort first by the first two
characters as alphanumeric characters, then by the next one or two
characters as numeric digits. Since it appears that ALL of the characters
are in a single field, I'll guess that it is a "text" type data field. The
sorting you are getting is alphanumeric sorting.

To sort by separate portions, you need separate portions!

Try this, open a new query, add the table that holds this field, then add,
as fields:

LeftPart: Left([YourField],2)

and

NumericPart: Mid([YourField],3)

then sort on THESE.

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> Hi again
> (The day I am skillful enough to actually answer some of these questions!)
[quoted text clipped - 6 lines]
> detail for me to follow. If anyone could guide me though the proccess of this
> kind of sorting, I would really appreicate it. Thanks!

Reply to this Message

oliaccount - 30 Aug 2007 11:00 GMT

Sorry its taken so long for me to reply (we are busy at the moment). This
about does it, so thanks!
I just need to to move the first character of the second field onto the end
of the first to nail it. I should be able to figure that bit out. I'll reply
here again in a couple of days if not.
And thanks again, I've been looking for something comprehendable for ages!!

Reply to this Message

oliaccount - 30 Aug 2007 11:41 GMT

Heck.
I finally get it working, and now they decide they want it differently.
Apparantly the postcode now has to sort by numbers, such as LE1 1AB, LE2 1AB,
LE2 2AB, LE11 1AB, taking the numbers from the middle of the postcode and
sorting by them so everything is in number squence, like this: 1 1, 2 1, 2
2, 11 1. I hope I've made that clear.
Thanks for your help!

Reply to this Message

oliaccount - 30 Aug 2007 12:32 GMT

>Heck.
>I finally get it working, and now they decide they want it differently.
[quoted text clipped - 3 lines]
>2, 11 1. I hope I've made that clear.
>Thanks for your help!

Edit:

What I need is to take the middle portion and sort it (the 3rd to 7th
characters)

Reply to this Message

John Spencer - 30 Aug 2007 13:02 GMT

You can try some calculated fields to sort on.

Val(Mid(PostCode,3))

Val(Mid(PostCode, Instr(1,PostCode," ")))

Since Val will error on nulls, you probably need to add a test for that if
your EVER leave PostCode blank

IIF(IsNull(PostCode),Null, Val(Mid(PostCode,3)))

If you are doing this in a report then you need to put the formulas in the
reports sorting and grouping.
In report design
-- Select View: Sorting and Grouping from the menu
-- In the first row field expression should be
IIF(IsNull(PostCode),Null, Val(Mid(PostCode,3)))
-- In the second row field expression should be
IIF(IsNull(PostCode),Null, Val(Mid(PostCode, Instr(1,PostCode," "))))

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>>Heck.
>>I finally get it working, and now they decide they want it differently.
[quoted text clipped - 10 lines]
> What I need is to take the middle portion and sort it (the 3rd to 7th
> characters)

Reply to this Message

oliaccount - 31 Aug 2007 11:15 GMT

Thanks John. This is EXACTLY what I needed. I'm definity writing down
everything you guys have given me on this.

Reply to this Message