Divide a field into 3 parts.

[FatMan]

Hi all:
I have some data that has been imported into an access database from an
Excel spreadsheet. One of the fields in the table (created from an Excel
file) is an address field in the following format: Add1, Add2, Add3.

What I would like to do is append the data from the new table (created from
Excel) into an existing table that has 3 fields for the address. Each part
of the address (Add1, Add2, Add3) would go to one of the 3 address fields in
the existing table. For example: ExistingTable.Address1 = Add1,
ExistingTable.Address2 = Add2, etc.

Can anyone tell me how to divide the one field into its three parts based on
the comma?

Any help is greatly appreciated.

Thanks,
FatMan

 

[Marshall Barton]

I have some data that has been imported into an access database from an
Excel spreadsheet. One of the fields in the table (created from an Excel
file) is an address field in the following format: Add1, Add2, Add3.

What I would like to do is append the data from the new table (created from
Excel) into an existing table that has 3 fields for the address. Each part
of the address (Add1, Add2, Add3) would go to one of the 3 address fields in
the existing table. For example: ExistingTable.Address1 = Add1,
ExistingTable.Address2 = Add2, etc.

Fields with multiple values are a pain.

Use three calculated fields in your append query.

Add1: Split(address, ",")(0)
Add2: Split(address, ",")(1)
Add3: Split(address, ",")(2)

 

[John Spencer]

Marshall,
Can you use those expressions directly in a query?

It looked like a good technique, so I attempted to use it and keep getting
an error (Access 2000).

I can write a UDF and pass in the string, the delimiter, and the subscript
and use that, but I can't find a way to do it directly in the SQL.

 

[FatMan]

Marshall:
I have tried your example below and get the following errors:

1) If I include the "(0)", I get "The exspression you entered has an invalid
.. (dot) or ! operator or inivalid parentheses.

2) If I exclude the "(0)", I get "Undefined function 'Split' in exspression.

Can you let me know what I am doing wrong.

Thanks,
FatMan

 

[Marshall Barton]

My fault, I tested it in the wrong environment. SQL doen't
know what to do with the f(...)(N) syntax.

As John said, create a Public function in a standard module:

Public Function GetPart(addr As Variant, part as Integer)
On Error GoTo ErrorHandler
If IsNull(addr) Then
GetPart = Null
Else
GetPart = Split(addr, ",")(part)
End If
ExitHere:
Exit Function

ErrorHandler:
Select Case Err.Number
Case 9
GetPart = Null
Case Else
MsgBox Err.Number & " - " & Err.Description
End Select
Resume ExitHere
End Function

Then the query field would be like:
Add3: GetPart(address, 2)

 

[Marshall Barton]

Marshall,
Can you use those expressions directly in a query?

It looked like a good technique, so I attempted to use it and keep getting
an error (Access 2000).

I can write a UDF and pass in the string, the delimiter, and the subscript
and use that, but I can't find a way to do it directly in the SQL.

You're right John. I tested in the wrong environment.
Thanks for catching that and for suggesting the way around
it.

 

[John Spencer]

I slapped together a vba function that you can call. Not fully tested.
Paste the following function into a module.

In your SQL you should be able to enter

Field: Address1: fGetSection([TheAddressField],",",1)
Field: Address2: fGetSection([TheAddressField],",",2)
Field: Address3: fGetSection([TheAddressField],",",3)


Public Function fGetSection(StrIn As Variant, _
Optional strDelim As String = ",", _
Optional IntSection As Integer = 1)

Dim sArray As Variant

If Len(StrIn & vbNullString) = 0 Then
fGetSection = StrIn

Else

sArray = Split(StrIn, strDelim)

'adjust in case array is not zero-based
IntSection = IntSection - 1 + LBound(sArray)

If IntSection < LBound(sArray) Then
fGetSection = Null

ElseIf UBound(sArray) < IntSection Then
'Handle asking for a section that doesn't exist
fGetSection = Null
Else
fGetSection = sArray(IntSection - LBound(sArray))
End If
End If
End Function