Parsing Text (Instr)

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Tirelle said:
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?
Click to expand...

There is a built in function Split() that will do that for you.
 
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=","
 
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
 
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
 
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
'====================================================
 
Back
Top