Converting a Combined Address to Standard Address Delivery fields

[DDPMF]

I'm trying to convert an address field that contains stores the Street Number, Directional (If Applicable) Indicator, Street Name, and Unit Number (If Applicable), but I do not know of a where I could possibly parse through the Address field in MS access to convert the string into separate fields (Street Number, Directional Indicator, Street Name, Unit Type, Unit #.

Here are two examples of an address in the existing database:
Address
Record 1: 2710 S Adams St #209
Record 2: 3336 S Wakefield St Unit # A

 

[CaptainD]

There are going to be problems since people are not standard on how they enter things. For example you have "#209" and then "# A" noting the space bewteen the # sighn and the letter A

With that, take a look at using the InStr() fuction.

http://support.microsoft.com/kb/209045

http://www.techonthenet.com/access/functions/string/instr.php

http://office.microsoft.com/en-us/access/HA012288571033.aspx

 

[HarleyQuinn]

This might be overkill (I'm sure I always type this before I opst a Regular Expression solution ) butI've knocked up a UDF using Regular Expressions to parse the field.

To use the UDF paste the following code into a module:

Option Explicit

Public Enum Addresses
    StreetNumber = 0
    DirectionInd = 1
    StreetName = 2
    UnitNo = 3
End Enum


Public Function ParseAddress(fAddress As String, fOrdinal As Addresses) As String

Dim re As Object
Dim mc As Object
Dim m As Object
Dim strMatches(3) As String

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "\b\d+\b|(\b\w{1}\b)|(\b\w.+(?=#)|\b\w.+(?!#))|(#.+)"
    Set mc = .Execute(fAddress)
End With

strMatches(0) = mc(0)
If Len(mc(1)) = 1 Then
    strMatches(1) = mc(1)
    strMatches(2) = mc(2)
Else
    strMatches(1) = "No DirectionInd"
    strMatches(2) = mc(1)
End If

If Left(mc(mc.Count - 1), 1) = "#" Then
    strMatches(3) = mc(mc.Count - 1)
Else
    strMatches(3) = "No UnitNo"
End If

ParseAddress = strMatches(fOrdinal)

End Function

Basically, this splits the field up and the puts it into an array (adding a value if any of the optional fields aren't present).

If you'd like a fuller explanation of the function please post back and I'll be happy to supply one)

You can call the function from your query specifying which field you want to return using the Enum values listed e.g.

UnitNumber: ParseAddress([YourField],UnitNo)

Like I say, this may be a bit much but it's good to keep myself in practise

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.