selecting partial data from field value

[TracyG]

I am working with a hyperlink data field and I need to pull out the first
portion of the field value. Is there a way to select all text before a
certain character or series of characters? For example, taking all
characters before the # sign in the field value.

Here's a sample of the data in the field:

c:\testdata\Form questions.doc#c:\testdata\Form%20questions.doc#

 

[Steve Schapel]

Tracy,

Using your example, you would use...
Left([YourField],InStr([YourField],"#")-1)

 

[SirPoonga]

There's a couple of ways to do this in VBA code.

First is to use the Mid() funciton to find the first occurance of the
character, then use left() and right() to grab the data on either side
of it once you know the position.

OR the super simple way is to use teh split function. Split using the
character as a delimiter. You now have an array where each value is
the text between the delimiters. IE in your example:
mystring = "c:\testdata\Form
questions.doc#c:\testdata\Form%20questions.doc"
myarr = split(mystring, "#")
debug.print myarr(0) ' this contains "c:\testdata\Form questions.doc"
debug.print myarr(1) ' this contains "c:\testdata\Form%20questions.doc"

 

[SirPoonga]

Errr, I meant InStr(), not Mid(). What Steve said