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 (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 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

710 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