Replace

[EllenM]

Hello,
Is there a way for me to remove everything that precedes the word "GRAS
notice"? I think this can be done with the replace function in a query.

What I ultimately want is the number that follows "GRAS notice", which I
know how to isolate using regular expressions. (Among other things, regular
expressions can isolate the first occurrence of a number.)


Thanks in advance,
Ellen

 

[John Spencer]

The following expression returns a string that starts after the first
instance of "GRAS Notice" in a field. If there the phrase "GRAS Notice"
does not appear in the field, then it will return a string that starts at
the 12th character.

Mid(SomeField, Instr(SomeField,"GRAS Notice") + 11)


To return the either the entire string or the truncated string you can use
the following expression.
IIF([SomeField] Like "*Gras Notice*",
Mid([SomeField], Instr([SomeField],"GRAS Notice") + 11), [SomeField])

If you know RegEx and how to use it, shouldn't you be able to do all this
with a RegEx solution?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[EllenM]

Thanks so much!!! Now that you've showed me how to remove stuff that precedes
"GRAS notice", I've been able to write a statement that does the whole thing:
Expr3: rgxExtract(IIf([Field2] Like "*Gras
Notice*",Mid([Field2],InStr([Field2],"GRAS Notice")+11),[Field2]),"\d+",0)

(Using John Nurick module that you gave me some months ago.)


Ellen

 

[fredg]

Hello,
Is there a way for me to remove everything that precedes the word "GRAS
notice"? I think this can be done with the replace function in a query.

What I ultimately want is the number that follows "GRAS notice", which I
know how to isolate using regular expressions. (Among other things, regular
expressions can isolate the first occurrence of a number.)

Thanks in advance,
Ellen

The replace function will replace the words "Gras notice" but if you
have text preceding that it will not remove that previous text.

NewColumn:Mid([FieldName],InStr([FieldName],"Gras notice")+ 12)

should return everything after the "Gras notice" text.