Hi,
Not sure which would be easiest to be honest. It has to go back into access, so may as well do it there.
Here is the way i'd do it in code, my tables are called _Cities and _Addresses
<code>
Option Compare Database
Private strNewAddress As String
Private strCityName As String
Sub Split_Address()
Dim strSQL As String
Dim rstCities As New ADODB.Recordset
Dim rstInspection As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim lngAddressID As Long
strSQL = "Select Distinct [City_Name] from [_Cities];"
Set con = Application.CurrentProject.Connection
rstCities.Open strSQL, con, 1
While Not rstCities.EOF
strCityName = rstCities.Fields("City_Name").Value
' Check column 1
strSQL = "SELECT Address_ID,[_Addresses].Address_1 FROM _Addresses;"
rstInspection.Open strSQL, con, 1
While Not rstInspection.EOF
If InStr(1, rstInspection.Fields("Address_1").Value, strCityName) <> 0 Then
lngAddressID = rstInspection.Fields("Address_ID").Value
strNewAddress = Remove_City(strCityName, rstInspection.Fields("Address_1").Value)
Update_Table "Address_1", lngAddressID
End If
rstInspection.MoveNext
Wend
rstInspection.Close
' Check column 2
strSQL = "SELECT Address_ID,[_Addresses].Address_2 FROM _Addresses;"
rstInspection.Open strSQL, con, 1
While Not rstInspection.EOF
If InStr(1, rstInspection.Fields("Address_2").Value, strCityName) <> 0 Then
lngAddressID = rstInspection.Fields("Address_ID").Value
strNewAddress = Remove_City(strCityName, rstInspection.Fields("Address_2").Value)
Update_Table "Address_2", lngAddressID
End If
rstInspection.MoveNext
Wend
rstInspection.Close
' Check column 3
strSQL = "SELECT Address_ID,[_Addresses].Address_3 FROM _Addresses;"
rstInspection.Open strSQL, con, 1
While Not rstInspection.EOF
If InStr(1, rstInspection.Fields("Address_3").Value, strCityName) <> 0 Then
lngAddressID = rstInspection.Fields("Address_ID").Value
strNewAddress = Remove_City(strCityName, rstInspection.Fields("Address_3").Value)
Update_Table "Address_3", lngAddressID
End If
rstInspection.MoveNext
Wend
rstInspection.Close
rstCities.MoveNext
Wend
rstCities.Close
Set rstCities = Nothing
Set con = Nothing
End Sub
Function Remove_City(strCityName As String, strAddress As String) As String
Dim intStartPosition As Integer
Dim intEndPosition As Integer
intStartPosition = InStr(1, strAddress, strCityName)
intEndPosition = intStartPosition + Len(strCityName)
Remove_City = Mid(strAddress, 1, intStartPosition - 1)
Remove_City = Remove_City & Mid(strAddress, intEndPosition, (Len(strAddress) - (intEndPosition - 1)))
End Function
Sub Update_Table(strFieldName As String, lngAddressID As Long)
Dim strSQL As String
strSQL = "UPDATE [_Addresses] SET [_Addresses]." & strFieldName & " = '" & _
strNewAddress & "', " & _
"[_Addresses].Address_City = '" & strCityName & "' " & _
"WHERE [_Addresses].Address_ID=" & _
lngAddressID & ";"
DoCmd.SetWarnings False: DoCmd.RunSQL strSQL: DoCmd.SetWarnings True
End Sub
</code>