Sort order problem in Aphanumeric field

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I have a query built on a table that has an address field that has
information sorted such as:

123 Day Street
675 Bay Ave.
2423 Night Drive
3426 Day Street
1345566 Bay Ave.
5649999 Yellow View Ave

What I want to do is sort the information Ascending by Street name then by
street number within Street Name. Hence, the above data should look like
this after I run the appropriate expression:

675 Bay Ave
1345566 Bay Ave
123 Day Street
3426 Day Street
2423 Night Drive
5649999 Yellow View Ave

How do I create an expression that performs the above?

Any assistance is greatly appreciated.
 
This should get it for you.

SELECT Addresses.Address
FROM Addresses
ORDER BY Right([Address],Len([Address])-Len(Val([Address]))-1),
Val([Address]);

You have to make two calculated fields to do the sorting.
 
Not Gonna Do It!

First, the expression - Len(Val([Address]))-1) - will error. You can't do a
Len on a numeric value.
Try this (untested):
ORDER BY Right([Address],Len([Address])-Len(Cstr((Val([Address])))-1),

There is a gotcha, though it may be rare, you may encounter addresses like
123B Main would sort to B Main 123
or even more rare
15 1/2 Bonzo Blvd = 151 /2 Bonzo Blvd
And let's not foget the crowd favorite:
1524 3rd st = 15243 d st

Wayne Morgan said:
This should get it for you.

SELECT Addresses.Address
FROM Addresses
ORDER BY Right([Address],Len([Address])-Len(Val([Address]))-1),
Val([Address]);

You have to make two calculated fields to do the sorting.

--
Wayne Morgan
MS Access MVP


Frank said:
I have a query built on a table that has an address field that has
information sorted such as:

123 Day Street
675 Bay Ave.
2423 Night Drive
3426 Day Street
1345566 Bay Ave.
5649999 Yellow View Ave

What I want to do is sort the information Ascending by Street name then by
street number within Street Name. Hence, the above data should look like
this after I run the appropriate expression:

675 Bay Ave
1345566 Bay Ave
123 Day Street
3426 Day Street
2423 Night Drive
5649999 Yellow View Ave

How do I create an expression that performs the above?

Any assistance is greatly appreciated.
Click to expand...
Click to expand...
 
I have a query built on a table that has an address field that has
information sorted such as:

123 Day Street
675 Bay Ave.
2423 Night Drive
3426 Day Street
1345566 Bay Ave.
5649999 Yellow View Ave

What I want to do is sort the information Ascending by Street name then by
street number within Street Name. Hence, the above data should look like
this after I run the appropriate expression:

675 Bay Ave
1345566 Bay Ave
123 Day Street
3426 Day Street
2423 Night Drive
5649999 Yellow View Ave

How do I create an expression that performs the above?
Click to expand...

Klatuu's concerns are all valid; in addition you'll find that

1223 W Main St.
1226 E Main St.

will sort with "Waco Avenue" and "Eastleigh St." rather than with
"Main".

In my biggest current database, I split up addresses further:
AddressNo, Direction, Street, Suffix so they can be sorted or searched
individually.

For the SIMPLE quick and dirty sort, given your current data, you can
use two calculated fields:

StreetName: Mid([Address], InStr([Address], " ") + 1)
StreetNo: Val([Address])

and sort by these two fields. All the "problem cases" mentioned in
this thread will remain problems though!

John W. Vinson[MVP]
 
Klatuu,

You're right about the different address formats. However, I did try the
query and it did work on the sample data. The Len() function did not error
out.
 
Yes, they will. Address parsing is a pretty complex affair. In 1986, I did
a food home delivery system that used the caller's address to determine to
which store the order would be routed.
The address parsing was the most difficult part of the coding. You have to
allow for common prefixes (N, North, NE, etc), Suffixes(pretty much the
same), Street Types(Rd, Blvd, St, Loop, Ave, etc). To get all the official
addressing rules, we used info from the Post Office. Then we started pulling
it apart character by character starting both from the Left and from the
Right of the string to evaluate it. It got pretty sophisticated and got
almost everything, but still , there were a few that would fall though. For
example, in Houston, Texas, a valid address was:

1551 West Loop South

The code caught the number (1551), Saw West as a Prefix ( the street name is
West Loop), Loop as a street type, and South as a Suffix, and would come up
with an error that there was no street name.

So, the short of it is that parsing addresses is even harder than parsing
names.

Good Luck
John Vinson said:
I have a query built on a table that has an address field that has
information sorted such as:

123 Day Street
675 Bay Ave.
2423 Night Drive
3426 Day Street
1345566 Bay Ave.
5649999 Yellow View Ave

What I want to do is sort the information Ascending by Street name then by
street number within Street Name. Hence, the above data should look like
this after I run the appropriate expression:

675 Bay Ave
1345566 Bay Ave
123 Day Street
3426 Day Street
2423 Night Drive
5649999 Yellow View Ave

How do I create an expression that performs the above?
Click to expand...

Klatuu's concerns are all valid; in addition you'll find that

1223 W Main St.
1226 E Main St.

will sort with "Waco Avenue" and "Eastleigh St." rather than with
"Main".

In my biggest current database, I split up addresses further:
AddressNo, Direction, Street, Suffix so they can be sorted or searched
individually.

For the SIMPLE quick and dirty sort, given your current data, you can
use two calculated fields:

StreetName: Mid([Address], InStr([Address], " ") + 1)
StreetNo: Val([Address])

and sort by these two fields. All the "problem cases" mentioned in
this thread will remain problems though!

John W. Vinson[MVP]
Click to expand...
 
Steve said:
First, the expression - Len(Val([Address]))-1) - will error. You can't do a
Len on a numeric value.
Click to expand...

Works fine for me.
Click to expand...


Very interesting!

I think this is another issue of differences between VBA,
the Expression Service, and the immediate window.

In VBA:
Dim xx As Long
xx = 123
Debug.Print Len(xx) displays 4 (the no. bytes in a Long)

In the Immediate Window
?Len(123) generates an unrelated error

In a query
Len(123) works as it should
 
Interesting, but weird. I guess this is my lesson for the day. In all the
Non Microsoft versions of Basic I have used, it only applies to a string, so
I have never tried to use it anywhere else.

Marshall Barton said:
Steve said:
First, the expression - Len(Val([Address]))-1) - will error. You can't do a
Len on a numeric value.
Click to expand...

Works fine for me.
Click to expand...


Very interesting!

I think this is another issue of differences between VBA,
the Expression Service, and the immediate window.

In VBA:
Dim xx As Long
xx = 123
Debug.Print Len(xx) displays 4 (the no. bytes in a Long)

In the Immediate Window
?Len(123) generates an unrelated error

In a query
Len(123) works as it should
Click to expand...
 
Yep. As a variation on the theme...

CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) VALUES (
Len(123) )"
.... appends 3

Dim xx As Long
xx = 123
CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) SELECT Len("
& xx & ")"
.... appends 3

Dim xx As Long
xx = 123
CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) VALUES ( " &
Len(xx) & " )"
.... appends 4

Dim xx As Integer
xx = 123
CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) VALUES ( " &
Len(xx) & " )"
.... appends 2
 
Screw it, I'm going back to COBOL :)

Steve Schapel said:
Yep. As a variation on the theme...

CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) VALUES (
Len(123) )"
.... appends 3

Dim xx As Long
xx = 123
CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) SELECT Len("
& xx & ")"
.... appends 3

Dim xx As Long
xx = 123
CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) VALUES ( " &
Len(xx) & " )"
.... appends 4

Dim xx As Integer
xx = 123
CurrentDb.Execute "INSERT INTO MyTable ( MyNumberField ) VALUES ( " &
Len(xx) & " )"
.... appends 2
Click to expand...
 
Thanks all for most informative and helpful comments. I tried Wayne's method
and it worked out pretty good for me. Not perfect but liveable.
I hate to deal with address types of sorts but must plow through for the
better good.

Once again, thanks tons for everyone's timely assistance.
 
1551 West Loop South

The code caught the number (1551), Saw West as a Prefix ( the street name is
West Loop), Loop as a street type, and South as a Suffix, and would come up
with an error that there was no street name.
Click to expand...

LOL!!!

You really wonder if some diabolical city planner did that
deliberately just to mess with us computer folks' heads...!

And then of course you have to deal with *mistakes* - people who live
on Maple Avenue and write down Maple St....

John W. Vinson[MVP]
 
Back
Top