Looking for a Function to help Seperate Values 1

[campbere]

I am trying to solve the following problem.

I need to take values from a resultset that can have the following values:

C3
D4-00;C2-01
D2-01;CA-98
D2-03;FG-99;C0-00
C1-09;C4-02;CD-08;C4

and when there is a semicolon split them and add them to a combo box. So I would have

C3
D4-00
C2-01
D2-03
FG-99
C0-00
C1-09
C4-02
CD-08
C4

I thought I had the solution but I was wrong. My code uses position of the ; and
not how many characters there are to the next semicolon. So I would get a value
like FG-99;C0-00.

I am hoping someone might know of a funtion that I am unaware that
can help me.

Here is my code:

while (not RSlst.eof)
cd = RSlst("code&quot

tmp = ""

'trim any leading or trailing spaces
tmp = trim(cd)

fst = 1

'Get the first position of the semicolon, if no semicolon the
'value is zero.
semi = Instr(tmp, ";&quot

'This while is only if there are more than one semicolon, indicating
'two or more values.
while (semi <> 0)

'This works fine if there is only one semicolon but
'if there are two then it doesn't work. If there
'are two this statement would have tmp2 = FG-99;C0-00
semi2 = mid(tmp, fst, semi -1)

Ctrl.AddItem tmp2

fst = semi + 1

'This statement starts at the character right after the last
'semicolon.
semi = Instr(fst, Tmp, &quot;;&quot

wend

tmp2 = mid(tmp, fst)

Ctrl.AddItem tmp2

RSlst.movenext
wend

If you know of a function that could help me I would be grateful.

 

[RickSpr]

This is the same algorithm I would use. The only thing I see wrong with it is that the statement:
semi2 = mid(tmp, fst, semi -1)
should be:
tmp2 = mid(tmp, fst, semi - 1)

Also, in VBA6 (Access 2000) there is a Split() function that will break a string into an array of substrings delimited by a chosen character. You might want to consider that if you're using Access 2000. Rick Sprague

 

[campbere]

Woops that was a typo. semi2 should be tmp2.

I am using Oracle, but will look into the split function.

 

[campbere]

The split function is also available in VB. I used it and it works. Thanks.