Selecting data between two sets of characters

[rudeamy]

Hi All:

This is my first post...

I need to extract data (that is different in all cases, see below) from between two sets of data that appear anywhere within a single field and copy it to another field.

For example, I have a field called SecondLine and here is the full data value for three of its records:

Hello my name is Rudeamy and I love the color orange.
Hey everyone, my name is Fred and I love the color red, but I also like purple.
Howdy, my name is Julia and I love the color lavender.

I need the data between "my name is" and "color" to appear in another field, called FirstLine.



(This isn't really the data I need to extract, but I think its an appropriate substitution.)

Any suggestions are greatly appreciated.

Amy

[orange]

You may wish to research

Instr() and Mid () functions in Access

http://www.techonthenet.com may have info

[rudeamy]

Won't instr() just give me a numeric position of the characters I need? I've tried mid()-- (at least at the basic level)--and that doesn't seem to be working. I'm guessing this involves building a Module, which I'm not equipped to do. . .

[orange]

Yes, you're right

Instr(...) gives you a position where a string A starts, say X
and Instr(xxxx) gives you the position of where the second string starts, say Y

So if you know where the strings X and Y start, You could extract the string between X and Y, and adjust for the length of the "marker string you searched for", to a field of your choice.

If you're planning on doing any string manipulation and not using some functions and expressions, you will be inventing something new. Sting manipulation invovles functions , expressions and modules.

Here are a few links
http://www.599cd.com/tips/access/bre...tr/default.asp
http://www.databasedev.co.uk/access-...functions.html

[rudeamy]

Yup. This is what I thought. I'm going to need some big guns. Thanks for your help.

[orange]

I need the data between "my name is" and "color" to appear in another field, called FirstLine.

Let's say this text is in a field called - MyString

so if "my name is" is in MyString, it starts at
Instr(MyString,"my name is") let's just say it's position 15.
and if "color" is in MyString, it starts at Instr(myString,"color")
and let's say that returns 37.
Now, the Mid() function
In Access, the Mid function extracts a substring from a string (starting at any position).

The syntax for the Mid function is:

Mid ( text, start_position, number_of_characters )

Text is the string that you wish to extract from.
Start_position indicates the position in the string that you will begin extracting from. The first position in the string is 1.
Number_of_characters indicates the number of characters that you wish to extract. If you omit this parameter, the Mid function will return all characters after the start_position.

So the string you want is in MyString, after "my name is" and before "color".
Using Mid, it becomes

Mid(MyString,Instr(MyString,"my name is") + 11,Instr(MyString, "color") -1)
The 11 represents the characters taken up by "my name is" (10) and then 1 char beyond that. The - 1 indicates the end of the string you want is 1 char before "color".

[rudeamy]

It worked. I cannot thank you enough!!