Solved

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

Posted on 2011-03-25
8
577 Views
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?
0
Comment
Question by:Grffster
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
0
 
LVL 8

Assisted Solution

by:pdd1lan
pdd1lan earned 50 total points
Comment Utility
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 450 total points
Comment Utility
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 = ""
    Else
        If Position > UBound(strArray) Then
            fnParse = ""
        Else
            fnParse = strArray(Position)
        End If
    End If
   
End Function
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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.

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Grffster
Comment Utility
That's the problem I'm getting at the moment as some have blank values...i.e. no address.
0
 

Author Comment

by:Grffster
Comment Utility
Silly me...hadn't catered for nulls, wasn't blanks. Yuk!!!
0
 

Author Comment

by:Grffster
Comment Utility
Understood on the point of the postcode as not every record has it. Good point though.
0
 

Author Closing Comment

by:Grffster
Comment Utility
Thanks to all who responded so quick. Saved me a big headache.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now