Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
590 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 49

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
0
 
LVL 8

Assisted Solution

by:pdd1lan
pdd1lan earned 200 total points
ID: 35214742
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1800 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 = ""
    Else
        If Position > UBound(strArray) Then
            fnParse = ""
        Else
            fnParse = strArray(Position)
        End If
    End If
   
End Function
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 77

Expert Comment

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

0
 

Author Comment

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

Author Comment

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

Author Comment

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

Author Closing Comment

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

926 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