• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

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

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
Grffster
Asked:
Grffster
2 Solutions
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
peter57rCommented:
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
 
GrffsterAuthor Commented:
That's the problem I'm getting at the moment as some have blank values...i.e. no address.
0
 
GrffsterAuthor Commented:
Silly me...hadn't catered for nulls, wasn't blanks. Yuk!!!
0
 
GrffsterAuthor Commented:
Understood on the point of the postcode as not every record has it. Good point though.
0
 
GrffsterAuthor Commented:
Thanks to all who responded so quick. Saved me a big headache.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now