Hi,
I need to retrieve a portion of an string in ms access, say for eg: str1='D_H_P_109' in the given string str1 in need to read all the characters following the 3rd underscore, how could I do in in MS Access? Are there any inbuilt functions like Instr().

Hi,

   You don't want the InStr() function since it only returns a number telling you the first character in the string where your string appears. So if you are looking for "109" in your string, then you would only have a 6 or 7 returned. With InStr, you check to see if a <>0 is returned and then get the data, which still needs another function.

If you know that you are always going to need the info after the third underscore, you need an Array with a Split() function. So in your case:

'======================================
NewArray = Split(str1, "_")
    strYourData = NewArray(3)
'======================================

The syntax is "ArrayName = Split(yourString, <character to split on>)"

This will break your data into this array:
D
H
P
109

Arrays always start numbering elements with 0, so since your data is in the fourth element, then you want 3.

If you have subsequent underscores, you can concatenate those elements from the array after you capture them. Like:

'===============================================
NewArray = Split(str1, "_")
   strYourData = NewArray(3) & "_" & NewArray(4)
'===============================================

Don't worry about buffer overflows with this array since in VBScript, the arrays are dynamic unless otherwise declared.

I hope this helps.


mmcdonal

The Split is a good idea. But beware of cases where you don't have enough characters to split on. Good practice...

On Error Resume Next
element = Split(strInput,"_")(3)
if Err.Number = 9 then ' Subscript out of range
   ' there were not enough "_" in strInput
else
   ' element contains what you want
endif

BTW, you can use the InStr format in a loop by searching for the string using the Start parameter of InStr. E.g.

j = 0
For i = 1 to 3
    j = Instr(j+1,strInput,"_")
Next I
element = Mid(strInput,j+1)

But with the Split function, why bother? Besides, the loop doesn't include error handling.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org