Splitting an address field

Allan320 · June 27th, 2006, 03:49 AM

I have an address all in the one line at the moment. My front end has been programmed this way. Now I have to split the address into 3 or 5 lines. The address looks like this:

3 Thorn Road Edinburgh Scotland G68 2AA

Some are spaced with a space and some have a return press between the lines.

Does anyone know how to do this so that it doesnt just take in 3 thorn etc

Thanks

Allan

SerranoG · June 27th, 2006, 06:58 AM

Read the help for the SPLIT, LEFT, RIGHT, and MID functions. You should be able to do something with those.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Allan320 · June 29th, 2006, 03:54 AM

Ok this is still getting the better of me. Some of the addresses that the users have put on the system have been seperated using spaces, return keys and commas. Some have no house numbers etc. So im stuck on how to do this as the query i have is giving me the wrong data into the fields.

Allan

SerranoG · June 29th, 2006, 06:53 AM

Try working your way from back to front.

Start with the right end. You know that the postal code is of a certain length and format. Look for that. Then work your way backward through country, city, etc. If you can identify the city (at least), then the address won't matter because you will then just take everything left of the city to the beginning, whether there is a number or not.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Allan320 · June 29th, 2006, 07:24 AM

The post code is in a seperate field so that makes it easier for me.

This is the query i have.

UPDATE Clients SET Clients.Address1 = Left([Address],InStr([Address],",")), Clients.Address2 = mid([Address],InStr([Address],",")), Clients.Address3 = Right([Address],InStr([Address],","));

If it was looking for this address: 34 Thornwood Road, Kilmarnock, Aryshire. It would produce

Address1 = 34 Thornwood Road,
Address2 = kilmarnock, Ayrshire ,
Address3 = nock, Ayrshire

tried all different ways and it meeses up

Thanks

allan

Allan320 · June 30th, 2006, 05:43 AM

Ok, got a new method of doing it. but still coming up with the same results :(

Code:

SELECT [Clients].[Address], Left([Clients].[address],InStr([Clients].[address],", ")) AS myNumeric, Mid([Clients].[address],InStr([Clients].[address],",  ")+1,Len([Clients].[Address])-InStr([CLients].[address],"APT")+InStr([Clients].[Address],", ")-1) AS myStreet, Right([Clients].[Address],InStr([Clients].[address],", ")) AS myApt
FROM Clients;

Anyone have any suggestions

Allan

mmcdonal · July 3rd, 2006, 07:46 AM

I would suggest taking all the data out into Excel and doing the ETL by hand.
Then rebuild that table structure to allow the proper input of the address fields.
Since each user has the opportunity to enter data however they want to, it is never going to work exactly right without tons of processing.

mmcdonal

Allan320 · July 4th, 2006, 05:31 AM

Yes, i know. We basically decided to start afresh. Add 5 fields into our form and get the users of the database to update the data as it is needed to be used. Only 1 record is used at a time, so it should be easy enough for them to update.

Thanks ;)

Allan

pjm · July 25th, 2006, 03:35 PM

I don't have any code for you but I do have a few suggestions:

    1. Write a parsing function in VBA to be used in a query. It's easier to work out
the code when you don't have to put it in one long expression in your SQL statement.

    2. I find that writing a simple grammar to completely describe my data helps:

    <address> ::= <street number> <street name> <street type> <town> <country> <postal code>
    <street number> ::= NUMBER | ORDINAL
    <street name> ::= STRING
    <street type> ::= Street | Road | Drive | Avenue | ...
    <town> ::= STRING
    <country> ::= STRING
    <postal code> ::= <3 letters or digits> BLANK <3 letters or digits>

-Phil-