Parse inconsistent String 1

[dpav29]

Hi,

I'm dealing with address fields which I normally don't have trouble with. . .I'll typically use Left, Mid, Right to pull out the component of the address (i.e. 123 main st, baltimore, MD 20122). Now I've got one that is inconsistent that I don't know what to do with.

I need to parse it to:

Address1
Address2
Zip

They strings are always comma delimited, but some have 3 parts and some have 4. They are either of 2 ways:

123 main st, baltimore, MD 20122
123 main st, suite 100, baltimore, MD 20122

I don't care about the city and state since I'll use the zip code for that. . .A simple Right(Trim([Address]),5) Gives me that.

I also won't have trouble with address1 because it is always to the left of the first comma (Left$([address,InStr(1,[ProvName],",")-1))

What I don't know how to do is to put an IIf in there somehow to pull address2 (using MID), but only when it is a four part address.

Has anyone done something like this before?

Thanks so much for any help!

 

[hneal98]

One way would be to count the number of ','. If there are three, then you know the next item after address1 is address2, but if there are only two, then the next item is the sate.

 

[Golom]

If you're trying to do this in code

Dim Parts() As String
Parts = Split (TheString, ",")
if Ubound(Parts) = 2 Then
   [COLOR=green]' Address1, City, State ZIP Format[/color]
   Address1 = Parts(0)
   Address2 = ""
   City = Parts(1)
   State = Left$(Parts(2),Instr(Parts(2)," ") - 1)
   Zip = Mid$(Parts(2),Instr(Parts(2)," ") + 1)
Else
   [COLOR=green]' Address1, Address2, City, State ZIP Format[/color]
   Address1 = Parts(0)
   Address2 = Parts(1)
   City = Parts(2)
   State = Left$(Parts(3),Instr(Parts(3)," ") - 1)
   Zip = Mid$(Parts(3),Instr(Parts(3)," ") + 1)
End If

Things become most unpleasant if you're trying to pull it off in SQL.

 

[hneal98]

There you go. Good piece of code Golom.

 

[dpav29]

Hey Golom, that looks awsome! I really appreciate your help. Unfortunately, I'm pretty green on VB. In trying to introduce the function into an update query, I"m pretty clueless. Could you point me in the right direction?

 

[Crowley16]

you can put this in a global module, then just call the function in a query, like you would a standard access function...

make sure you declare the function to be global though...

--------------------
Procrastinate Now!

 

[Golom]

If you want to do this in a query then you'll need to make some changes. A query is "field at a time" processing so a function that returns 5 parameters as this one would won't do you much good. Try something like this

Public Function AP (TheString As String, ThePart As Integer) As String
Dim Parts() As String
Parts = Split (TheString, ",")
[COLOR=green]
' ThePart = 1    - Return Address 1
'         = 2    - Return Address 2
'         = 3    - Return City
' etc.
[/color]
if Ubound(Parts) = 2 Then
   [COLOR=green]' Address1, City, State ZIP Format[/color]
   Select Case ThePart
      Case 1: AP = Parts(0)
      Case 2: AP = ""
      Case 3: AP = Parts(1)
      Case 4: AP = Left$(Parts(2),Instr(Parts(2)," ") - 1)
      Case 5: AP = Mid$(Parts(2),Instr(Parts(2)," ") + 1)
   End Select
Else
   [COLOR=green]' Address1, Address2, City, State ZIP Format[/color]
   Select Case ThePart
      Case 1: AP = Parts(0)
      Case 2: AP = Parts(1)
      Case 3: AP = Parts(2)
      Case 4: AP = Left$(Parts(3),Instr(Parts(3)," ") - 1)
      Case 5: AP = Mid$(Parts(3),Instr(Parts(3)," ") + 1)
   End Select
End If
End Function

Then your Query looks like

Select AP(AddrField,1) As Address1,
       AP(AddrField,2) As Address2,
       AP(AddrField,3) As City    ,
       AP(AddrField,4) As State   ,
       AP(AddrField,5) As Zip

From tbl

Good Luck.