How to use Instr when there are three items in a field 1

[Pack10]

I have a field that is comma separated like so.... IB, TSS, WCOB
It can have one, two, or three values comma separated.
I have no issue if there are one or two values, but am having trouble
if the field conains the three values.

This is the code.


ictr = InStr(x, ",")

If ictr > 0 Then ' there is more than 1

first_value = Mid$(x, 1, ictr - 1)
Call Add_Current_Record(strMetric, first_value, rsMaster!ID)

second_value = Mid$(x, ictr + 1, 10)
Call Add_Current_Record(strMetric, second_value, rsMaster!ID)

next_value = InStr(second_value, ",")

If next_value > 0 Then
third_Value = Mid$(second_value, next_value + 1, 10)
Call Add_Current_Record(strMetric, third_Value, rsMaster!I
End If

Endif






I need to use Instr to pull it apart. I can handle the first split

 

[SkipVought]

hi,

Split()

dim a, i as integer
a = split(x,",")
for i = 0 to ubound(a)
  debug.print trim(a(i))
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Forget about InStr, use Split instead:

Dim x As String
Dim sValues() As String
Dim i As Integer

x = "IB, TSS, WCOB"
sValues = Split(x, ",")

For i = LBound(sValues) To UBound(sValues)
    Debug.Print Trim(sValues(i))
Next i

Have fun.

---- Andy

 

[MajP]

 Dim numberOfItems As Integer
 Dim ictr() As String
 Dim x As String
 x = ""
 ictr = Split(x, ",")
 numberOfItems = UBound(ictr) + 1
 If numberOfItems > 0 Then
  call add_Current_Record (strMetric, ictr(0), rsMaster!ID)  
 End If
 If numberOfItems > 1 Then
  call add_Current_Record (strMetric, ictr(1), rsMaster!ID) 
 End If
 If numberOfItems > 2 Then
  call add_Current_Record (strMetric, ictr(2), rsMaster!ID) 
 End If