Parsing Text (Instr)

[Guest]

Hi,
I am working ona function that will seperate a string of Integers delimited
by commas (i.e. 1,2,3,4,5,6,56,128) for me so I can work with the individual
elements. I am trying to use Instr() but I keep getting an "Invalid
Procedure Call or Argument" error.
The syntax i am using is: WhereIam = InStr(0, SearchString, ",",
vbTextCompare)
I will use the variable WhereIam to start the next call of Instr(). What am
I doing wrong?

Thank You

 

[Rick Brandt]

Hi,
I am working ona function that will seperate a string of Integers delimited
by commas (i.e. 1,2,3,4,5,6,56,128) for me so I can work with the individual
elements. I am trying to use Instr() but I keep getting an "Invalid
Procedure Call or Argument" error.
The syntax i am using is: WhereIam = InStr(0, SearchString, ",",
vbTextCompare)
I will use the variable WhereIam to start the next call of Instr(). What am
I doing wrong?

There is a built in function Split() that will do that for you.

 

[Guest]

Can you give me a little more help with Split or Instr? I currently have
WhereIam as an Integer datatype. Split() give me a type mismatch. Her eis
the call:
WhereIam = Split(Me.CellsJumperedOut, StrSearch, ,vbTextCompare) where
StrSearch=","

 

[Guest]

Hi Tirelle

Better to use the Split function:

Try:

WhereIAm = Split(SearchString, ",")

You can then recover individual values from the WhereIAm array, e.g.
varResult = WhereIAm(3)
will return the value of 4 (using your example string - note that the first
element is WhereIAm(0).

Cheers.

BW

 

[John Nurick]

Split() returns an array in which each element contains one item from
the list. E.g., in the Immediate pane try:

arItems = Split("1,2,3,4,5,6,7", ",")
Debug.Print arItems(0)
1
Debug.Print arItems(1)
2
Debug.Print arItems(6)
7

If you want to pick one item at a time from the list and you don't
always know the number of items, use this function:

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)
End Function

In the Immediate pane again:

?SafeSplit("1,2,3,4,5,6,7", ",", 0)
1
?SafeSplit("1,2,3,4,5,6,7", ",", 6)
7
?SafeSplit("1,2,3,4,5,6,7", ",", 99)
Null

 

[John Spencer]

Specific answer to your question is that the first argument (if present)
must be > 0. So Instr(0,...) will fail with an error.

As noted elsethread, the SPLIT function will probably be better if you
are using later versions of Access (2000 and later if I recall correctly)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================