Sort text field in ascending order

Topic

New Reply

Using Access 2003 (2000 format)

The field is text data type. It stores a street number. I can’t make it a numeric field because the street number could be something such as 5D or 12C rather than 982 or 13.

Using Blue Street as an example, there are 2 addresses on Blue Street by which I want to sort.
9 Blue Street
41 Blue Street

In a report these end up sorted as
41 Blue Street
9 Blue Street

It’s because Access sorts by the left-most number.

How do I fix this so that the addresses will sort correctly?

(Of course, I can’t use Descending sort order, because that throws other addresses out of proper sort order – e.g. 12 Jane St. and 20 Jane St.)

Thanks.

Tom

Reply | Quote

Replies

If there is always a number in the first position you can create a formula that will pad the entry with zeros then sort on that field.

SortColumn: Right(“00000″ & Left([address],InStr([address],” “)-1),5) & Mid([address],InStr([address],” “),99)

Reply | Quote

Works perfectly!
I am using SortColumn: Right(“00000″ & Left([FullAddress],InStr([FullAddress],” “)-1),5) & Mid([FullAddress],InStr([FullAddress],” “),99)

Here’s an anomaly.

The report sort order is…
Street (prefix characters 6, ASC)
FullAddress (each value) — This is what you called address

All Streets and addresses work out correctly except for the following…
5434 Wellington Rd. 39
7748 Wellington Rd. 22
then 2 addresses on Wellington Rd. 29

Obviously, these are all separate street addresses.

If I increase the # of prefix characters, I get too many separate streets.
If I, instead of prefix characters, I group on Each Value, I get too many separate streets in cases where some entries are Ave. and other entries are Avenue, or St. and Street, etc.

Other than going in the program and entering code to make sure that all avenues are entered as Ave. and all streets are entered as St., etc. is there a way to fix this?

Tom

Reply | Quote

All Streets and addresses work out correctly except for the following…
5434 Wellington Rd. 39
7748 Wellington Rd. 22
then 2 addresses on Wellington Rd. 29

How should they sort?

If I increase the # of prefix characters, I get too many separate streets.

Not sure what you mean by too many sheets. The formula should be a hidden field and can be placed anywhere on the canvas (in the appropriate section of course).

Reply | Quote

Re…
5434 Wellington Rd. 39
7748 Wellington Rd. 22
then 2 addresses on Wellington Rd. 29

The issue isn’t an incorrect sorting. The issue is that Wellington Rd. 39, Wellington Rd. 22 and Wellington Rd. 29 are all separate and distinct streets

There is a Street footer section, that forces a new page after the Street section. So each of these should show on a separate page.

re…
“not sure what you mean by too many sheets”
I didn’t use the word “sheets” but I assume you meant to type “streets.”
What I meant by changing the # of prefix characters results in too many streets is that, with the forcing of a new page after the Street footer section, Oxford Street and Oxford St. are seen by the program as two separate and distinct streets where, in fact, they are the same.

Does that clarify?
Tom

Reply | Quote

Oiy. If you sit back from your computer and squint your eyes the tr in streets looks like an h. I really need to get my glasses checked

As far as the Wellington Rd. problem…
You could split out the house number and the rest of the address into two different fields. Apply the padding to the house number field. Then sort on the street name and then the house number.

As far as the Ave vs. Avenue vs. Av. …
That would probably have to be a manual process as far as changing them to a consistent format.

Reply | Quote

The HouseNbr and Street are 2 separate fields to begin with.

I’ll have to figure out how to pad the HouseNbr. in the query, in order that the sorting comes out properly in the report. Without the padding the HouseNbr itself doesn’t sort properly in the report.

I figured I’d have to work out a process whereupon there is consistency of entry with Avenues and Streets, Roads., etc.

(my glasses get out of whack too often for my liking…and I hate to blame it on my eyes themselves!)

Tom

Reply | Quote

In the query I plugged in this column
SortHouseNbr: Format([HouseNbr],”0000″)

That does the padding, but it makes no difference in the situation
5434 Wellington Rd. 39
7748 Wellington Rd. 22
then 2 addresses on Wellington Rd. 29

They still come out as the same street, where they are separate streets.

Tom

Reply | Quote

I also tried this padding…
Right(“000” & [HouseNbr],10)

Same result.

Tom

Reply | Quote

Without seeing your actual set up I can only make guesses. Can you post a sanitized version of your database with enough data to demonstrate the problem.

Reply | Quote

mbarron
Thanks for the offer of uploading a scaled down database, but it’s not the easiest thing to do.

Actually, since it’s only this one page of streets that prints out this way, I am deciding to leave it alone. Just not worth the trouble in going further, and I don’t want to make a career out of one report, so we will live with what I have.

Thanks a lot for all of your help. It is very much appreciated.

Tom

Reply | Quote

I think I understand the problem. From your description, you are not using the FullAddress field (e.g. “Wellington Rd. 39”) for the page break, but the field Street which is equal to the first 6 characters of the FullAddress field (in my example “Wellin”). This is a clever way to group on the same page records which most probably belong to the same street but where the address is not properly normalized: “Oxford Street” and “Oxford St.” have the same first six characters (“Oxford”) and will end up on the same page.

I guess in your case there is no simple solution. Address normalization is quite a complex matter in databases. I have dealt in the past with a census database and we had a very sophisticated algorithm to standardize the address and to store each information in separate fields: the house number (which can contain letters or text like “bis” and “ter”!), the street type (“Street”, “Avenue”, …) the street name (“Oxford”, “Wellington”, …). And you will have to deal with ambiguity: an avenue called Saint Paul may be written “St. Paul Avenue” (the street of Mr. Paul Avenue?) or “Saint Paul Av.”, so blindly replacing “St.” by “Street” may give funny results…

Reply | Quote

Stephane
You have captured the picture correctly. And there is no simple solution. But since these particular collection of streets (Wellington Rd. 22, 29 and 39) is the only anomaly, I’m going to let them appear on the same print-out page.

Tom

Reply | Quote