Assuming Access 2000 or later, use the Split() function to parse the string.
Split() generates an array, so Split() on the break character first, and
look at the last array element (defined by UBound.)
Then Split() that string on the space character to break it into separate
words. You could then loop through the elements of this array and use
IsNumeric() to locate the Zip code, and put the remaining array elements
together again as the Country.
If you are working in an older version of Access, the following code
demonstrates how to simulate the functionality of the Split() function:
Public Function Split(strIn As String, strDelim As String) As Variant
'Purpose: Return a variant array from the itmes in the string,
delimited the 2nd argument.
Dim varArray As Variant 'Variant array for output.
Dim lngStart As Long 'Position in string where argument starts.
Dim lngEnd As Long 'Position in string where argument ends.
Dim lngLenDelim As Long 'Length of the delimiter string.
Dim i As Integer 'index to the array.
lngLenDelim = Len(strDelim)
If (lngLenDelim = 0) Or (Len(strIn) = 0) Then
ReDim varArray(0) 'Initialize a zero-item array.
Else
ReDim varArray(9) 'Initialize a 10 item array.
i = -1 'First item will be zero when we add 1.
lngStart = 1 'Start searching at first character of
string.
'Search for the delimiter in the input string, until not found any
more.
Do
i = i + 1
If i > UBound(varArray) Then 'Add more items if necessary
ReDim Preserve varArray(UBound(varArray) + 10)
End If
lngEnd = InStr(lngStart, strIn, strDelim)
If lngEnd = 0 Then 'This is the last item.
varArray(i) = Trim(Mid(strIn, lngStart))
Exit Do
Else
varArray(i) = Trim(Mid(strIn, lngStart, lngEnd - lngStart))
lngStart = lngEnd + lngLenDelim
End If
Loop
'Set the upper bound of the array to the correct number of items.
ReDim Preserve varArray(i)
End If
Split = varArray
End Function
Function TestSplit()
Dim var1 As Variant
Dim i As Integer
var1 =
Split("Title=1;TableName=2;FieldName=3;a=64;b=65;c=66;d=67;e=68;f=69;g=70;h=71;i=88;
j=999", ";")
For i = LBound(var1) To UBound(var1)
Debug.Print var1(i)
Next
Debug.Print UBound(var1)
End Function
