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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ali - 29 Nov 2007 08:56 GMT

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

\

Signature

Allen Phailat Wongakanit

Reply to this Message

Allen Browne - 29 Nov 2007 09:47 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a table:
>
[quoted text clipped - 30 lines]
> 1009 Milk no
> 1010 Acid Extrac yes

Reply to this Message

Ofer Cohen - 29 Nov 2007 09:55 GMT

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")

-----
Good Luck
BS"D

> I have a table:
>
[quoted text clipped - 31 lines]
>
> \

Reply to this Message

Duane Hookom - 29 Nov 2007 15:56 GMT

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.

Signature

Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

> I have a table:
>
[quoted text clipped - 31 lines]
>
> \

Reply to this Message

John W. Vinson - 29 Nov 2007 18:51 GMT

>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]

Reply to this Message