Parsing an address field

[John Baker]

Can anyone please help?

I have a column which contains Addresses in the format:
Suburb State Postcode

Each name is separated by a space.
The States are one of 7 Australian States.

A suburb name might be more than one word.

Can anyone suggest a way to parse each address into 3 separate fields. One
for each of Suburb, State and Postcode.

Not sure whether to try to do it in a query or perhaps use a VBA function



Any help greatly appreciated.

John Baker.

 

[John Vinson]

Can anyone please help?

I have a column which contains Addresses in the format:
Suburb State Postcode

Each name is separated by a space.
The States are one of 7 Australian States.

A suburb name might be more than one word.

And of course so will a State (New South Wales) - or is this in your
table as NSW? And (pardon my ignorance) what's the format of
Australian postcodes - a number, or like Canadian or British
(something like X9X 9X9)?

Can anyone suggest a way to parse each address into 3 separate fields. One
for each of Suburb, State and Postcode.

Not sure whether to try to do it in a query or perhaps use a VBA function

Depending on the answer to the question above, it might be either.
Also, what version of Access are you using? I think that A2002 has a
function InStrRev (which can find the LAST blank in a text string,
i.e. the one before the postcode), and pick it apart from there...

 

[Tom Ellison]

Dear John:

If you're writing for an MDB, I'd go with using a VBA function to do
this. If you're writing an ADP then I'd write this as a UDF.

You can load up an array of the 7 States using values in a table.
Search the address string for the portion of the string that matches
the name of each State until you find one. At that point you have
found the center portion of the values, that is, the State. What
comes before that is the Suburb. What follows is the Postcode.

It should be moderately easy.

One trick is to make sure the array loads only once, not each time the
function is called. If you make it a Static array you can check it
see if it is empty or has been loaded each time you call the function.
Just load it when empty.

This assumes there will be no changes in the States. If you have
forgotten one or misspelled one, you'll have to close the application
and reopen it so the function will be forced to load the array again.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts