Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 21st, 2004, 08:12 AM
Registered User
Join Date: Dec 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Instring function in ms access

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().
Old December 21st, 2004, 08:27 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts


   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:

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.

Old December 24th, 2004, 02:06 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

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
   ' element contains what you want

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access VBA Dateddiff Function SKZ Access VBA 5 November 18th, 2008 08:38 AM
Calling MS Access vba function via Excel Kourosha Excel VBA 0 December 31st, 2007 08:33 AM
Create Trigger,Procedure, Function in MS-Access.? navneet_chauhan10 SQL Language 1 December 27th, 2007 09:33 AM
MS ACCESS 2003 FRONTEND AND MS SQL SERVER 2005 DB mohankumar0709 SQL Server 2005 3 March 23rd, 2007 12:48 AM
Syntax of date function in MS Access krishreddy Access 3 July 25th, 2006 03:42 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.