Divide a Text Address Field in an Access Table

[SarahMH]

Can anyone help. Within a table I have an address field which has 3 parts of an address split by a comma e.g. 132 Sunny Ave,Barns,Chester. This data needs to be split in an automated manner to enable a report which displays the address as 3 separate fields - Address1 Address2 and Address3.
Can anyone shed some light - I hope it could be done within the report query?

Thanks in advance

 

[lespaul]

as long as there is a comma between each field something like this should work:


SELECT LEFT(Address, InStr(Address,",") - 1) As FirstPart, Mid(Address, InStr(Address, ",") + 1, InStrRev(Address, ",") - 1) As MiddlePart, Right(Address, InStrRev(Address, "," + 1) As LastPart From tblName

Leslie

 

[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, ",")
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 in the query grid:
Address2: getAddressPart([Address field], 2)
to get the Address2 value.

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