Hi xCav8r!
Thanks a lot for you help!
I'm trying to change a part of your code because I would
like also to add this field "Old.PostalAddress " into the new table.
Therefore I have changed the following lines (bold) but an error occurs
Option Compare Database
Option Explicit
Option Base 1
Const strcBlankSpace As String = " "
Const strcComma As String = ","
Sub ManipulateRecordSets()
Dim db As DAO.Database
Dim rstOld As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim strSQL As String
Dim varaMyParsedAddress As Variant
Set db = CurrentDb
strSQL = "SELECT Old.PostalAddress FROM tblPostalAddresses_OLD as Old;"
Set rstOld = db.OpenRecordset(strSQL)
strSQL = "SELECT New.txtStreetName, New.txtBuildingNumber, New.txtZip, " _
& " New.txtCity,Old.PostalAddress FROM tblPostalAddresses_New as New,tblPostalAddresses_OLD as Old;" Set rstNew = db.OpenRecordset(strSQL)
With rstOld
If .BOF = False And .EOF = False Then
Do While .BOF = False And .EOF = False
varaMyParsedAddress = ExtractAddressElements _
(.Fields!PostalAddress)
With rstNew
.AddNew
.Fields!txtStreetName = varaMyParsedAddress(1)
.Fields!txtBuildingNumber = varaMyParsedAddress(2)
.Fields!txtZip = varaMyParsedAddress(3)
.Fields!txtCity = varaMyParsedAddress(4)
.Fields!PostalAddress = varaMyParsedAddress(5)
.Update
End With
.MoveNext
Loop
End If
End With
End Sub
Function ExtractAddressElements(strStringToParse As String) As Variant
Dim intNumberOfCommas As Integer
Dim varaAddress As Variant
Dim varaStreetAddress As Variant
Dim varaZipCity As Variant
Dim strStreetAddress As String
Dim strBuildingNumber As String
Dim strStreetName As String
Dim strZip As String
Dim strCity As String
Dim intNumberOfStreetAddressElements As Integer
intNumberOfCommas = NumberOfOccurrencesInString(strStringToParse, strcComma)
If intNumberOfCommas >= 1 Then
varaAddress = Split(strStringToParse, strcComma, -1, vbTextCompare)
Select Case intNumberOfCommas
Case 1
' RULE: Street Name + (Comma) + Building Number
' + (Comma) + Zip + (Space) + City
' EG: Washington's Avenue 78,1000 Brussels
' EVERYTHING BEFORE THE COMMA
' EG: Washington's Avenue 78
' TRIMMING GETS RID OF UNWANTED LEADING AND TRAILING SPACES
strStreetAddress = Trim(varaAddress(0))
' ELEMENT = SET OF CONTIGUOUS CHARACTERS
' EG: Washington's
' EG: Avenue
' EG: 78
' SPACES DIVIDE ELEMENTS
varaStreetAddress = Split(strStreetAddress, strcBlankSpace, _
-1, vbTextCompare)
' NUMBER OF ELEMENTS IN EVERYTHING BEFORE THE COMMA
' EG: Washtington's = 0
' EG: Avenue = 0 + 1 = 1
' EG: 78 = 0 + 1 + 1 = 2
' (This array counts from zero)
intNumberOfStreetAddressElements = UBound(varaStreetAddress)
' BUILDING NUMBER IS LAST ELEMENT BEFORE COMMA
' EG: 78
strBuildingNumber = varaStreetAddress _
(intNumberOfStreetAddressElements)
' IF THERE ARE MORE THAN TWO ELEMENTS, THEN THE STREET
' NAME IS COMPOUND
' EG: Washington's Avenue
If intNumberOfStreetAddressElements > 1 Then
' SHAVE THE TOP ELEMENT OFF THE ARRAY
' IE, GET RID OF THE BUILDING NUMBER
ReDim Preserve varaStreetAddress(0 To _
intNumberOfStreetAddressElements - 1)
' PUT EVERYTHING ELSE BACK TOGETHER AND CALL IT
' STREET NAME
strStreetName = Join(varaStreetAddress, strcBlankSpace)
' THERE'S ONLY ONE ELEMENT; THEREFORE, ONE WORD
Else
strStreetName = varaStreetAddress(LBound(varaStreetAddress))
End If
' EVERYTHING AFTER THE COMMA
varaZipCity = Split(varaAddress(UBound(varaAddress)))
' FIRST ELEMENT AFTER COMMA
strZip = Trim(varaZipCity(LBound(varaZipCity)))
'SECOND ELEMENT AFTER COMMA
' ASSUMES CITY NAME IS NOT COMPOUND
strCity = Trim(varaZipCity(UBound(varaZipCity)))
Case 2
' Street Address (separated by spaces with the last contiguous
' set of characters = Building Number)
' + (Comma) + Zip + (Space) + City
' EG: Washington's Avenue, 78, 1000 Brussels
' EVERYTHING BEFORE FIRST COMMA
strStreetName = Trim(varaAddress(0))
' EVERYTHING BEFORE SECOND COMMA
strBuildingNumber = Trim(varaAddress(1))
' THIS IS INCLUDED IN CASE YOU DON'T WANT TO PARSE
' THE STREET NAME AND BUILDING NUMBER
strStreetAddress = strStreetName + strcBlankSpace _
+ strBuildingNumber
' EVERYTHING AFTER THE SECOND COMMA
varaZipCity = Split(varaAddress(UBound(varaAddress)))
' FIRST ELEMENT AFTER THE SECOND COMMA
strZip = Trim(varaZipCity(LBound(varaZipCity)))
' SECOND ELEMENT AFTER THE SECOND COMMA
' ASSUMES CITY NAME IS NOT COMPOUND
strCity = Trim(varaZipCity(UBound(varaZipCity)))
End Select
End If
'RETURN TO SENDER :)
ExtractAddressElements = Array(strStreetName, strBuildingNumber, _
strZip, strCity)
End Function
Function NumberOfOccurrencesInString( _
strStringToSearch As String, _
strSearchForThis As String)
Dim intPositionOfSoughtString As Integer
Dim intStartPosition As Integer
' START INITIAL SEARCH FROM THIS POSITION
intStartPosition = 1
' DETECT ALL OCCURRENCES
Do
intPositionOfSoughtString = InStr(intStartPosition, strStringToSearch, _
strSearchForThis, vbTextCompare)
' AUGMENT COUNTERS ONLY WHEN STRING IS FOUND
If intPositionOfSoughtString <> 0 Then
NumberOfOccurrencesInString = NumberOfOccurrencesInString + 1
' CHANGE STARTING POSITION OF SEARCH TO LOOK AT THE PARTS OF THE
' STRING THUS FAR UNEXAMINED
intStartPosition = intPositionOfSoughtString + 1
End If
' IF InStr RETURNS 0, THEN IT WASN'T FOUND
Loop Until intPositionOfSoughtString = 0
End Function
' Previous Rules:
' Examples:
' strStringToSearch = "Street of Washington,78, 1000 Brussels"
' strStringToSearch = "Street of Washington,78, 1000, Brussels"
' strStringToSearch = "Mainstreet 4, 75000, PARIS"
' ElseIf intNumberOfCommas = 2 Then
' strStreetAddress = Trim(varaAddress(0))
' varaStreetAddress = Split(strStreetAddress, strcBlankSpace,-1, vbTextCompare)
' intNumberOfStreetAddressElements = UBound(varaStreetAddress)
' strBuildingNumber =varaStreetAddress (intNumberOfStreetAddressElements)
' If intNumberOfStreetAddressElements > 1 Then
' ReDim Preserve varaStreetAddress (intNumberOfStreetAddressElements - 1)
' strStreetName = Join(varaStreetAddress, strcBlankSpace)
' Else
' strStreetName =varaStreetAddress (LBound(varaStreetAddress))
' End If
' strZip = Trim(varaAddress(1))
' strCity = Trim(varaAddress(2))
' ElseIf intNumberOfCommas = 3 Then
' strStreetName = Trim(varaAddress(0))
' strBuildingNumber = Trim(varaAddress(1))
' strZip = Trim(varaAddress(2))
' strCity = Trim(varaAddress(3))
What could I do?
Fran?ois