Extracting a word from a long text field

[Leo]

I have a text field (up to 200 characters) and I need to extract a specific
word.
The text field name is "MEDICATIONS" and the word I need to extract which
might appear anywhere in the field is 'VENOFER'. The word need to be entered
in a separate field in a query.

Could any one help as to how to enter the code in the query?

Thanking you
Leo

 

[Leo]

In the criteria I entered Like "VENOFER" Or "Venofer"
It seems to work.
Will it cause any problems down the line? In other wrods is this an
acceptable procedure?

 

[John Spencer]

I hope you entered
Like "*Venofer*"
as the criteria. If you are using the native Access data engine (JET or ACE)
then they are not case-sensitive.

If you entered
Like "Venofer"
then you are only going to get records where that is the exact content of the
field - that is it won't find a record where the field's content is "Venofer
version x"

As far as returning just the word Venofer in a separate field (column) of the
query you would need something a calculated field.

Field: Venofer: "Venofer"

If you do not want to filter the records but just return Venofer if it is
present in the Medications field, then you would need a calculated column like
the following.
Field: Venofer: IIF([Medications] Like "*Venofer*","Venofer",Null)

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

 

[Leo]

Thank you John. As always you have been of great help.
Thanking you again
Leo

I hope you entered
Like "*Venofer*"
as the criteria. If you are using the native Access data engine (JET or ACE)
then they are not case-sensitive.

If you entered
Like "Venofer"
then you are only going to get records where that is the exact content of the
field - that is it won't find a record where the field's content is "Venofer
version x"

As far as returning just the word Venofer in a separate field (column) of the
query you would need something a calculated field.

Field: Venofer: "Venofer"

If you do not want to filter the records but just return Venofer if it is
present in the Medications field, then you would need a calculated column like
the following.
Field: Venofer: IIF([Medications] Like "*Venofer*","Venofer",Null)

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

In the criteria I entered Like "VENOFER" Or "Venofer"
It seems to work.
Will it cause any problems down the line? In other wrods is this an
acceptable procedure?

.

 

[Leo]

John

Your answers and and your methods have been simply outstanding! You are a
great teacher. I googled and also looked up in Amazon.com. But there was not
a single book on Access that you wrote. Is that really true and if so is
there anything forthcoming? If none is forthcoming I wish you would consider
publishing since it will be of great service for those of us who use Access
and are not database professional (but are professionals in other fields.)

Thanking you
Sincerely
Leo

I hope you entered
Like "*Venofer*"
as the criteria. If you are using the native Access data engine (JET or ACE)
then they are not case-sensitive.

If you entered
Like "Venofer"
then you are only going to get records where that is the exact content of the
field - that is it won't find a record where the field's content is "Venofer
version x"

As far as returning just the word Venofer in a separate field (column) of the
query you would need something a calculated field.

Field: Venofer: "Venofer"

If you do not want to filter the records but just return Venofer if it is
present in the Medications field, then you would need a calculated column like
the following.
Field: Venofer: IIF([Medications] Like "*Venofer*","Venofer",Null)

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

In the criteria I entered Like "VENOFER" Or "Venofer"
It seems to work.
Will it cause any problems down the line? In other wrods is this an
acceptable procedure?

.

 

[John Spencer]

I'm flattered.

No, I have not written any books and probably will not. I enjoy helping out
and will continue to do so as long as my skills are relevant.

There are quite a few good books available already. The tough part is that
any tutorial books cannot cover all the possible problems. They can only show
you what is available and give you some of the skills you need to use the tools.

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

John

Your answers and and your methods have been simply outstanding! You are a
great teacher. I googled and also looked up in Amazon.com. But there was not
a single book on Access that you wrote. Is that really true and if so is
there anything forthcoming? If none is forthcoming I wish you would consider
publishing since it will be of great service for those of us who use Access
and are not database professional (but are professionals in other fields.)

Thanking you
Sincerely
Leo

I hope you entered
Like "*Venofer*"
as the criteria. If you are using the native Access data engine (JET or ACE)
then they are not case-sensitive.

If you entered
Like "Venofer"
then you are only going to get records where that is the exact content of the
field - that is it won't find a record where the field's content is "Venofer
version x"

As far as returning just the word Venofer in a separate field (column) of the
query you would need something a calculated field.

Field: Venofer: "Venofer"

If you do not want to filter the records but just return Venofer if it is
present in the Medications field, then you would need a calculated column like
the following.
Field: Venofer: IIF([Medications] Like "*Venofer*","Venofer",Null)

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

In the criteria I entered Like "VENOFER" Or "Venofer"
It seems to work.
Will it cause any problems down the line? In other wrods is this an
acceptable procedure?

:

I have a text field (up to 200 characters) and I need to extract a specific
word.
The text field name is "MEDICATIONS" and the word I need to extract which
might appear anywhere in the field is 'VENOFER'. The word need to be entered
in a separate field in a query.

Could any one help as to how to enter the code in the query?

Thanking you
Leo

.