if a field contains "vita" or "acid" , show "YES" to the result co

[ali]

I have a table:

consists "ID" , "Product name" and "Result".

IF the colum "Product_name" contains word "vita" or "Acid", the next column
should show "YES" else "it should be "NO"



ID Product_name
1001 Vitamin
1002 Vitamilk
1003 Calcium
1004 Fat
1005 Sodium
1006 HB Acid
1007 Omega Acid
1008 Grape Extract
1009 Milk
1010 Acid Extrac




-------------------------------------------------------------------------------
Expected Outcome:


ID Product_name If "vita" or "acid" is found ?
1001 Vitamin yes
1002 Vitamilk yes
1003 Calcium no
1004 Fat no
1005 Sodium no
1006 HB Acid yes
1007 Omega Acid yes
1008 Grape Extract no
1009 Milk no
1010 Acid Extrac yes






\

 

[Allen Browne]

Remove the Result field from your table.

Then create a query. In query design view, type an expression this into the
Field row (all on one line):
Result: If([Product_name] Like "*vita*" Or [Product_name] Like
"*acid*"), True, False)

Save the query. Use it anywhere you might need the Result field.

If you want the query to show the word yes or no, set the Format property of
the field in the query.

 

[Ofer Cohen]

As a new field in the query try something like

NewFieldName: IIf([Product_name] Like "*Acid*" Or [Product_name] Like
"*vita*",True,False)

NewFieldName: IIf([Product_name] Like "*Acid*" Or [Product_name] Like
"*vita*","Yes","No")

 

[Duane Hookom]

IMHO, I wouldn't hard-code these values (vita and acid) into any expression
in a query unless I was sure this business rule would never, ever change.
Minimally, I would create a small user-defined function that would
encapsulate the business rule into one-place. Ideally, there should be a
table of values to compare against.

 

[John W. Vinson]

IF the colum "Product_name" contains word "vita" or "Acid", the next column
should show "YES" else "it should be "NO"

I agree with Duane that this is a dangerous rule. What if you had a product
named "Inevitable Success" or "Placidity Tea"?

John W. Vinson [MVP]