Extract text from a field

[murleyj]

I have a field named [Descrip]



There are thousands of records in the table in which it resides...table name [Routing]

Using Access Query design, i need to search for records in the [Descrip] field that begin specifically with the word "PROG " (minus the quotation marks) and then extract every bit of text from the first space to the first carriage return.

Is this possible? The DATA TYPE of this field is identified as "Long Text".

What do I need to put in the query to return just the text I need?


Thank you!

[lfpm062010]

i need to search for records in the [Descrip] field that begin specifically with the word "PROG " (minus the quotation marks)

Use the query criteria "PROG *" (Need quotatrion marks) will search all the records begin with the word "PROG ".

extract every bit of text from the first space to the first carriage return.

Use the Mid function and search for Chr(13) and/or Chr(10) will find the first carriage return and line feed.

Need help on the Mid function. Look here http://www.techonthenet.com/access/f...string/mid.php

[June7]

Will also need InStr function. http://www.techonthenet.com/access/functions/index.php

Mid([Descrip], 6, InStr(Chr(13)) - 1)

[murleyj]

Thanks, guys. But I am not sure how to put all of this together in the Query builder in Access. See the below picture for what I currently have:

[June7]

Create another field with the example expression. Put the expression in a cell on the Field row of the grid.

[murleyj]

Thanks again, but I don't believe I follow....this is what I am getting:

[June7]

Sorry for my typo:

Mid([Descrip], 6, InStr([Descrip], Chr(13)) - 6)

Also have to subtract 6, not 1. I tested in VBA immediate window and this worked.

Consistency of data structure is critical in string manipulation. Also, the expression will error on an empty string and return Null if field is Null.

Carefully study the referenced links. With a little practice you will gain a better understanding of these functions and recognize these errors.

[murleyj]

Awesome. Thank you very much. That worked great!