Extracting data from a string 2

[doctorswamp]

Hi all

Have data field for addresses that contains the entire address, separated by commas

eg Hillside, New Road, Summertown, Exeter, Devon, EX12 3TY

I'd like to be able to chop this up into these components in a query

Address1
Address2
Address3
Town
County
Postcode

Help always appreciated

 

[Melagan]

Have a look at the Left(), Mid(), Right(), Instr(), and Len() functions =)

Example:

SELECT Left(t.yourAddress, Instr(t.yourAddress, ",")-1) as Address1

FROM tblYourTable as t;

That should give you the first part of your string up to the first comma.

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

In a standard code module create the following function:

Public Function getElem(str, delim As String, N As Integer)
If IsNull(str) Then Exit Function
Dim myArr
myArr = Split(str, delim)
If N >= 1 And N <= (1 + UBound(myArr)) Then
  getElem = myArr(N - 1)
End If
End Function

And now your SQL code:
SELECT getElem([address field],',',1) AS Address1
, getElem([address field],',',2) AS Address2
, getElem([address field],',',3) AS Address3
, getElem([address field],',',4) AS Town
, getElem([address field],',',5) AS County
, getElem([address field],',',6) AS Postcode
FROM yourTable

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

 

[Melagan]

That looks like a nice function PH, but I got a compile error on this line:

[COLOR=red]myArr = Split([b]str[/b], delim)[/color]

"ByRef argument type mismatch"

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

You may try this definition:
Public Function getElem(ByVal str, delim As String, N As Integer)

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

 

[Melagan]

Adding ByVal didn't help it compile, but adding ByVal and this modification DID allow it to compile:

Public Function getElem([COLOR=blue]ByVal[/color] str As [COLOR=blue]String[/color], delim As String, N As Integer)

 If IsNull(str) Then Exit Function
 Dim myArr
 myArr = Split(str, delim)
 If N >= 1 And N <= (1 + UBound(myArr)) Then
'            trim to get rid of spaces.
   getElem = [COLOR=red]Trim([/color]myArr(N - 1)[COLOR=red])[/color]
End If
End Function

~Melagan
______
"It's never too late to become what you might have been.

 

[doctorswamp]

Many thanks both of you. Am implementing now and will come back if any problems arise.