Filter out part of a text string

[Nancy]

I have a text field that contains patent information. Some records also
contain the phrase "See (page )". "See (page )" is never in the same place
in a string. How can I run a query that removes "See (page )" from the
results but leave the remainder of the text field?

I appreciate help!

 

[John Spencer MVP]

You could use the replace function if you were trying to replace an exact
expression)

Field: NoSeePage: Replace([YourTextField],"See (Page )")

Otherwise this gets a bit tricky. The following MIGHT work for you, but it
also might give you bad results depending on your data.

Field: NoSeePage: IIF([YourField Like "*See [0-9]*",
Left([YourField],Instr(1,[YourField], " See ")-1) &
Mid([YourField],Instr(Instr([Your Field]," See ")+6," ")),[YourField])

To really do this well, would require writing a complex VBA function or using
REGEX (regular expression). I'm not good enough with regular expressions to
give you the solution.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County