Sorting Addresses etc

[Gasman]

We often get queries here as to how one can sort data like addresses where 110 Magnolia Drive comes before 2 Magnolia Drive, as the data is text.

Just found this code here, that at least allows for a way around that and very easily to. Thank you Philip @sonic8

https://codekabinett.com/rdumps.php?Lang=2&targetDoc=logical-numerals-sorting-access-vba

I know the author frequents here, but I cannot remember his username. Edit: @AccessBlaster reminded me of the name.

The only problem is that it is for 64 bit Access as posted on that site, and I only use 32bit Access 2007.

I have amended it for my system by the code below. That is all that is needed and have tested it on my system.

#If VBA7 Then
        Private Declare PtrSafe Function LCMapStringEx Lib "Kernel32.dll" (ByVal lpLocaleName As LongPtr, ByVal dwMapFlags As Long, ByVal lpSrcStr As LongPtr, ByVal cchSrc As Long, _
                ByVal lpDestStr As LongPtr, ByVal cchDest As Long, ByVal lpVersionInformation As Long, _
                ByVal lpReserved As Long, ByVal sortHandle As Long) As Long
#Else
        Private Declare Function LCMapStringEx Lib "Kernel32.dll" (ByVal lpLocaleName As Long, ByVal dwMapFlags As Long, ByVal lpSrcStr As Long, ByVal cchSrc As Long, _
                ByVal lpDestStr As Long, ByVal cchDest As Long, ByVal lpVersionInformation As Long, _
                ByVal lpReserved As Long, ByVal sortHandle As Long) As Long
#End If

 

[AccessBlaster]

I know the author frequents here, but I cannot remember his username.

Is it sonic8?

 

[GPGeorge]

Is it sonic8?

Yes.

 

[Gasman]

Is it sonic8?

Yes, that is him. Thank you @AccessBlaster
I will add his username to the post.

 

[CJ_London]

Assuming the house number is at the start of the address I use a much simpler method

select address
From mytable
Order by val(address), address

If the address does not have a house number (perhaps has a house name), val will return 0 so those addresses will appear before ones that have a number

Not so easy if the address starts with 'Flat 1' - but there are relatively few options e.g. Flat, Appt, Apartment, etc so you can always use a nested replace function

Order by val(replace(replace(address,"Flat",""),"Appt","")), address

 

[Gasman]

Well the address was just my example.
The O/P was looking to sort a code in the format mentioned below.

The format of the number is 001-00 and is incremented by 1 (002-00, etc) through code each time a new record is added on the form.

The problem arises when reaching 999-00. The next record is then 1000-00. This record should appear at the end or the beginning of the list, according to the sort order set. It doesn't. Access puts it between 100-00 and 101-00.

 

[cheekybuddha]

Val() was one of the solutions offered - funnily enough, by Philipp Stiefell (@sonic8 )

 

[CJ_London]

Didn’t see that in the original link? Guess great minds think alike

 

[ebs17]

999-00

Would anyone have a problem with sorting if the text expression is broken down into two numbers and then sorted by the number columns?
This is probably called an additional normalization step.
@CJ_London's suggestions do the same thing: remove atomic elements so that they can easily act on them.
Philipp's solution is huge, but shouldn't be necessary in a well-structured database.

 

[Gasman]

Yes, that method was mentioned as well, but I have noticed newbies reluctant to do that, despite that being the correct way to go.
Also they wanted the leading zeroes.

 

[cheekybuddha]

Would anyone have a problem with sorting if the text expression is broken down into two numbers and then sorted by the number columns?

Original thread

 

[ebs17]

But there is always a huge urge to put together atomic information in a “smart” way, and many people find that great and natural. The fact that many people can't deal with the compound expressions at all or can only deal with them poorly is a different world that will only be entered later with astonishment or fright.

Beyond pure functionality: If the atomic information is already in its own table fields, you can bring index usage into play and ensure performance. Database work also involves a bit of higher mathematics.

SELECT Format(FirstNumber, "000") & "-" & Format(SecondNumber, "00") AS SmartExpression
FROM TabX
ORDER BY FirstNumber, SecondNumber

The user gets what he wants to see and use, but only on the interface available to him. How information is stored in tables and used in queries and code is none of the user's business.

 

[cheekybuddha]

You are repeating what has already been said

 

[ebs17]

One can't repeat it too often. I repeat not only others, but also myself. The problem itself is repeated over and over again, so the simple hint is not yet in enough minds.