Martin Griffiths
asked on
How to parse a single comma-separated address field in to constituent address line fields in Access 2010
I have a field in a table called Address. It contains data like 10 Acacia Avenue, Bananatown, Bananaland, BA12 B12. I need to split this up in to 6 new fields Address Line 1, Address Line 2, Address Line 3, Address Line 4, Address Line 5, Postcode.
I've started building this up slowly in my access query expression builder per address line field using Instr with commas etc, but I've got to Address Line 2 and it's getting a little hard to read already, and I've got another 4 increasingly more complex line fields to go!
I'm thinking there's got to be an easier way of doing this. Can someone help please?
I've started building this up slowly in my access query expression builder per address line field using Instr with commas etc, but I've got to Address Line 2 and it's getting a little hard to read already, and I've got another 4 increasingly more complex line fields to go!
I'm thinking there's got to be an easier way of doing this. Can someone help please?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The basic problem is that addresses are not consistently structured. You will have to decide on maximum number of lines you are going to accept, unless you get into more complex areas of data normalisation.
And because addresses have different lengths , you cannot guarantee that 'Postcode' will be the postcode when you try to get it.
The function posted by fyed is where you have to start from but that function has to deal with addresses of different lengths, so that you don't get an error when you ask for line 4 of a 3 line address. I guess it would need to return a "" if the line did not exist.
And because addresses have different lengths , you cannot guarantee that 'Postcode' will be the postcode when you try to get it.
The function posted by fyed is where you have to start from but that function has to deal with addresses of different lengths, so that you don't get an error when you ask for line 4 of a 3 line address. I guess it would need to return a "" if the line did not exist.
ASKER
That's the problem I'm getting at the moment as some have blank values...i.e. no address.
ASKER
Silly me...hadn't catered for nulls, wasn't blanks. Yuk!!!
ASKER
Understood on the point of the postcode as not every record has it. Good point though.
ASKER
Thanks to all who responded so quick. Saved me a big headache.
Public Function fnParse(FieldValue as string, Position as integer, Optional Delimiter as string = ",") as String
fnParse = Split(FieldValue, Delimiter)(Position)
End Function
Then use:
SELECT fnParse([Address], 0) as [Address Line 1],
fnParse([Address], 1) as [Address Line 2],
fnParse([Address], 2) as [Address Line 3]
FROM yourTable