Filter out part of a text string

N

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!
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MS ACCESS 2013 Long Text filed filter by another tables Long text field 1
Select Portion of a Text String 4
Copy only part of text string 4
Date filter 2
Create string of text from several fields eliminating last comma 6
Filter Fields Containing Values Found in Another Table 4
Using strings to filter database 4
VBA: How can I use a string variable as autofilter criteria? 4

Top