[Webinar] Streamline your web hosting managementRegister Today

x
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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