Extracting a word from a long text field
in [Queries]
|
Prev: CrossTab Query question
Next: pattern of data
From: Leo on 11 Apr 2010 00:45 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
From: Leo on 11 Apr 2010 01:08 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" wrote: > 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 >
From: John Spencer on 11 Apr 2010 09:38 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 wrote: > 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" wrote: > >> 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 >>
From: Leo on 11 Apr 2010 11:31 Thank you John. As always you have been of great help. Thanking you again Leo "John Spencer" wrote: > 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 wrote: > > 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" wrote: > > > >> 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 > >> > . >
From: Leo on 11 Apr 2010 17:03
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 "John Spencer" wrote: > 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 wrote: > > 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" wrote: > > > >> 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 > >> > . > |