Separate a comma delimited string

[dodgyone]

I have a string in my database as follows:

House, Street, County

What I would like to do is to be able to separate each of the string values into it's own field. Obviously using the ccommas to separate the text is the answer.

I know that this involves Left, Right, Mid and InStr but I'm not sure how it all comes together. Could someone explain the methods used in VB code to get the required output.

Thanks very much...

 

[MichaelRed]

try "SPLIT" see the help file/topic.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[FancyPrairie]

The following code should do it:

dim i as integer
dim k as integer
dim strHouse as string
dim strStreet as string
dim strCounty as string
dim strAll as string

strAll = "House,Street,County"

i = instr(strAll,",&quot
if (i > 0) then
strHouse = mid(strAll,1,i-1)
k = instr(i+1,strAll,",&quot
if (k > 0) then
strStreet = mid(strAll,i+1,k-(i+1))
strCounty = mid(strAll,k+1)
end if
end if

 

[FancyPrairie]

Didn't know about the Split function...cool

 

[dodgyone]

Thanks FancyPrairie for the help... it works great

Sorry to be a nuisance but I like to understand what I'm doing. Could you possibly explain the mid and instr methods used in the vb code please...

Cheers...

 

[sangeeth]

Try "F1- Help"!!!

 

[dodgyone]

I've tried the code as follows:

strAll = rst.Fields("LOCATION_LINE1&quot

j = InStr(strAll, ",&quot
If (j > 0) Then
rst.Fields("NamePri&quot = Mid(strAll, 1, j - 1)
k = InStr(j + 1, strAll, ",&quot
rst.Update
If (k > 0) Then
rst.Fields("NameSec&quot = Mid(strAll, j + 1, k - (j + 1))
l = InStr(j + 2, strAll, ",&quot
rst.Update
If (l > 0) Then
rst.Fields("Town&quot = Mid(strAll, k + 1)
rst.Update
End If
End If
End If

The problem is I'm getting the error:

Run-time error '3421': Data type conversion error

The fields rst.Fields("NamePri&quot are all vbstring with 60 characters so I can't see what the problem could be.

 

[dodgyone]

Hi.. I've solved the problem above. The field was not created properly in the VBA code

I've also noticed that in my string:

HOUSE,STREET,COUNTY

that sometimes it's:

HOUSE,STREET

Or:

HOUSE,STREET,STREET,COUNTY

How do I plan for these eventualities. So that if a second street appears then both streets go into STREET with the comman still inplace inbetween then. And, if just HOUSE,STREET then to ignore COUNTY completely.

 

[dodgyone]

Plus occasionally there is just HOUSE in the string address.

I'm totally stumped about how to plan for these eventualities.

Any help would be really appreciated...

 

[Rolliee]

Try this code:

MyStr = "HOUSE,CITY,STATE,COUNTRY"
Do While (Len(MyStr) > 0) And (InStr(MyStr, ",&quot > 0)
len1 = InStr(MyStr, ",&quot
NuStr = Mid(MyStr, 1, len1 - 1)
MsgBox NuStr
MyStr = Mid(MyStr, len1 + 1)
Loop

Rollie