Solved: query part of a field?

[debauch]

Hello,

In one field of my access table, the text is as follows:

"UserA has been in this particular status for Up for 10 minutes"

How would I be able to query it to only show "this particular status for"? The word "in" and "Up" will always preceed and proceed the "particular status".

So this query would be something like "select * between in and up"?

[CreganTur]

Take a look at the InStr and InStrRev functions - they allow you to look for the placement of a specified partial string within a greater string. By using these, you can get their location within the string. Then you can use Left and Right functions to grab only what you want.

Be sure to look at the documentation in Access Help for specifics on these functions.

HTH:thumb

[debauch]

Thanks, I'll give it a try.

[debauch]

Ok,

So I (with help of others) came up with something *close*..

Status: Mid([Body],InStr([Body],"in ")+Len("in "),Len([Body])-InStr([Body],"in ")+Len("in ")-InStr([Body],"Up ")+13)

The length of the text between in and up ("this particular status for") varies and is not a static 13 characters.

Is there a way, to capture everything inbetween the two words (in and up)?

[Norie]

Why does it matter if it isn't always 13?

You are using InStr to find where "in" and "Up", so why not use those results to extract the string using Mid and Len?

Mid([Body], InStr([Body], "in ")+3, InStr([Body], "Up ") -InStr([Body], "in "))

Note this is untested but hopefully it'll give you the gist.

[debauch]

niiiiice....

That worked perfect. I don't know why I still can't wrap my head around these! Thanks. I will feed off this for future queries for the same project I am working on.

[Norie]

Glad it worked.:)

Like I said I didn't test it and just looking at it just now I thouht oh-oh, shouldn't there be some Len, Right, Left etc going on in there.

Those functions probably could be used but when I do see them I always get the feeling the matter is being overcomplicated.