Postcode Sorting

  • Thread starter Thread starter oliaccount via AccessMonster.com
  • Start date Start date
O

oliaccount via AccessMonster.com

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

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
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!!
 
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!
 
oliaccount said:
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!
Click to expand...

Edit:

What I need is to take the middle portion and sort it (the 3rd to 7th
characters)
 
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," "))))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks John. This is EXACTLY what I needed. I'm definity writing down
everything you guys have given me on this.
 
Back
Top