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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

Dale Fye 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

626 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