remove carriage returns and move each part to another field 1

[jimmypur]

Hi

Hope somebody can help. I have something like 6,000 address records which have been imported into a database table which has a SQL backend and an Access front end.

Unfortunately these records were formatted as:

Address1: AddressLine1[cr]AddressLine2[cr]AddressLine3[cr]
Address2:
Address3:
City: City
Province: State
PostalCode: Zip Code
Country: Country

They have already been imported and are linked to other tables.

Is there a way to

1. Strip out the carriage returns and place each part of the address into their respective fields? ie

Address1: AddressLine1
Address2: AddressLine2
Address3: AddressLine3

Currently the fields Address2 and Address 3 are empty in most cases.

Any help would be great.

Thanks

jp

 

[PHV]

Create a public function in a standard code module like this:
Public Function getAddressPart(strAddress, intPart As Integer)
Dim tmpArr
If Len(Trim(Nz(strAddress, ""))) = 0 Then Exit Function
tmpArr = Split(strAddress, vbCr)
If intPart <= UBound(tmpArr) + 1 Then
getAddressPart = Trim(tmpArr(intPart - 1))
ElseIf intPart = 1 Then
getAddressPart = strAddress
End If
End Function
And then you can play like this:
getAddressPart([Address1], 2)
to get the Address2 value.
If you consider an update query, try it on an imported table before updating the linked one.

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

 

[evalesthy]

I like that routine - I'm going to save it for future reference. Have a star. Thanks.