Access 2000, how to sort addresses by street and suburb that exclude numbers?

[HowHow]

Using Access 2000. I need to group the address by suburb then by street name in my report.

Two major problems here:
1. Unit number has the word "Unit" before number
2. Semi detach house has "a" or "b" after number

Examples of the addresses as below. Address 1 means the first line/field for users to key address in line one, Address 2 means second line/field two for users to key address, normally village, park or street name

eg.1, Normal addaress:

Address 1: 54
Address 2: Cale Street
Suburb: Como


eg 2, the address of semi-d house:

Address 1: 10a
Address 2: Britan Street
Suburb: Como


eg 3, the address of apartment or unit:

Address 1: Unit 5, 72 Gordon Way
Address 2: Bentley Park
Suburb: Bentley


eg 4, the address that has park or village:

Address 1: 20 Graham Crescent
Address 2: Bentley Park
Suburb: Bentley

I have problem sorting them when they are not the normal address type. I created two combo box in my form to filter the suburb and street name and my report display all the address 1 and address 2 under specific Suburb.

What should I do to sort report by street names by "ignoring" the numbers in front for the address temporary, but at the same time, still showing the full address on report?

[emsik1001]

Hi

When I create Address Line 1 and Address Line 2 I always ask the people do you ever want to sort it? if not then its usually fine... otherwise I would create 'atomic' fields so I would add extra control like House Number, street name, unit (if applicable) etc... literally everything what I think is needed for sorting.

So in your case if you can do it I would consider redesigning the table.

Sorry if it doesn't help.

Otherwise you will have quite a few formulas to do the job and you would need to update it each time you get new scenario.

Regards
Emil

[HowHow]

Hi Emil,

I cannot redesign the table because I am using Access to connect to our main system thru ODBC, thus all the tables are from the main system and the tables are only linked to my Access for reports. Mainly because main system only have standard reports and cannot produce what my managers required.

Any idea about the code or method to sort the address?

[Stewart Ross]

Hi. Two comments here. Firstly, you will need to design a custom function in VBA to parse the addresses concerned and return a version on which you can sort. Such a function could then be used within a query to provide a calculated field on which your report could be sorted.

Secondly, you need to be very clear before you design the custom function what it is you need done, otherwise your function will not cover all possibilities. Write down as many examples as you can, and in doing so write down how you want them to appear after transformation.

As an example of the pitfalls of not defining your problem clearly consider your third example in post # 1. Simply removing the 'Unit 5' component still leaves you with '72 Gordon Way', which is not in the form you require it - at least until a further application of the same function has taken place.

Be aware that text-processing problems like this, based on messy and ill-structured real world data, are non-trivial and take a lot of effort to get right (if this is possible at all). If you expect to obtain a general solution that meets your needs you will need to be very careful in specifying the transformation involved before you take this further.

In my opinion there is only so far you will be able to go with this one, as users can and will have entered addresses such as 25 a Somestreet instead of 25a, which makes it difficult to parse where the real street name begins and the qualified number ends...

-Stewart

[emsik1001]

Hi If you use only few forms to input the data then you could consider adding extra fields + VBA code to move the data to the address line 1 + address line 2 when you type it // and use the additonal columns for sorting...

Can't see a better way at the moment :)

Regards
Emil