Function to delimit text strings

[DiazTek]

Not sure if that is the right way to put it but here is my problem.

I have a filed that contains text like this "500 N Main Street Townville SC 99999-9999"

I need to be able to use a function to delimit that data to be copied into other indvidual fields.

I need the data broken down and copied into other fields. Using the example above this is how I need the data moved

Data(Field Name)
-------------------------------
500 N Main Street(OwnerAddress)
Townville(OwnerCity)
SC(OwnerState)
99999-9999(OwnerZipCode)

I am using 4 SetValue commands in a Macro for those 4 fields. Becasue the address varies in description I need to work from right to left.

The first SetValue Expression would copy from the end of the string to the space right before the zip code.
The next SetValue Expression would start at the space right before the zip code and go to the second space right before the state.
The next SetValue Expression would start at the second space right before the state and go to the third space right before the city.
The last SetValue command would start at the third space right before the city and go to the beginning of the text string (regardless of the number of spaces in-between.)

An example of a function that does this would be great. I just don't know how to have an expression count spaces.

Is this possible?

 

[CajunCenturion]

Have you looked into using the Split function, and then working the array from the Upper bound to the lower bound.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[HaroldGE]

Use this function to find the last blank ...

Function LastOccurrence(strSearchString As String, _
            strLastOccurrence As String) As Integer
    Dim intVal As Integer, intLastPos As Integer
    intVal = InStr(strSearchString, strLastOccurrence)  'Find 1st
    Do Until intVal = 0                                 'Find next
        intLastPos = intVal                             'Keep last
        intVal=InStr(intLastPos+1,strSearchString, strLastOccurrence)
    Loop
    LastOccurrence = intLastPos                         'Return last
End Function

ie...
Txt=Address

i=LastOccurrence(Txt," ")
Zip=mid(Txt,i+1) 'Should be numeric (IsNumber())
Txt=mid(Txt,1,i-1) 'remove Zip

i=LastOccurrence(Txt," ")
State=mid(Txt,i+1) 'Should be 2 letters
Txt=mid(Txt,1,i-1) 'remove State

i=LastOccurrence(Txt," ")
City=mid(Txt,i+1)
Street=mid(Txt,1,i-1)

You can look for commas with ...
i=LastOccurrence(Txt,",") 'search for last comma
or
i1=LastOccurrence(Txt,",") 'search for last comma
i2=LastOccurrence(Txt," ") 'search for last blank
i=iif(i1>i2,i1,i2) 'the last blank or comma

 

[willir]

Are your rules for transferring the data into specific fields consistent?

Everytime I attempted to parse addresses, I was about 85 to 95% accurate which I feel is pretty good, but it took a lot of work.

For example:

29 1st Street, Apt2
2-29 1st Street
2-29 1st St.
2-29 1st St
2-29 First St

RR1
RR#1
RR #1

555 1st Street SW

Each of these are a little tricky.
Then there are spelling mistakes

I found that I had to create a generic parsing process, and then try to trap the exceptions.

Tools of the trade...
Parsing with
INSTR
LEFT
RIGHT
MID

LEN

FOR / NEXT loops

Then writing the data to the database
DAO or ADO

See Microsoft Access help pages for assistance. The first functions for parsing are pretty easy to use.

Good luck
Richard

 

[PHV]

Anyway, you may consider using VBA instead of old fashioned limited macros:
Dim a As Variant
a = Split([Field Name])
If UBound(a) >= 3 Then
OwnerZipCode = a(UBound(a))
OwnerState = a(UBound(a) - 1)
OwnerCity = a(UBound(a) - 2)
ReDim Preserve a(UBound(a) - 3)
OwnerAddress = Join(a)
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244