How to parse a single comma-separated address field in to constituent address line fields in Access 2010

Posted on 2011-03-25
Last Modified: 2012-05-11
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?
Question by:Grffster
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35214725
Try creating a function (fnParse) that looks like:

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

Assisted Solution

pdd1lan earned 50 total points
ID: 35214742
LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 450 total points
ID: 35214772
With a little error checking fnParse might look like:

Public Function fnParse(FieldValue As String, Position As Integer, Optional Delimiter As String = ",") As String

    Dim strArray() As String
    strArray = Split(FieldValue, Delimiter)
    If Position < 0 Then
        fnParse = ""
        If Position > UBound(strArray) Then
            fnParse = ""
            fnParse = strArray(Position)
        End If
    End If
End Function
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

LVL 77

Expert Comment

ID: 35214796
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.


Author Comment

ID: 35214882
That's the problem I'm getting at the moment as some have blank values...i.e. no address.

Author Comment

ID: 35215004
Silly me...hadn't catered for nulls, wasn't blanks. Yuk!!!

Author Comment

ID: 35215009
Understood on the point of the postcode as not every record has it. Good point though.

Author Closing Comment

ID: 35215025
Thanks to all who responded so quick. Saved me a big headache.

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question