Formula for data field that "does NOT contain" particular value 1

[hsvb]

I know there is a simple solution with Report Expert that is escaping me. If a data field has multiple possible values and you want to select that data field only if it DOES NOT contain one of the values, what is the formula?

 

[MalcolmW]

The Select Expert, rather than the Report Expert?
The select expert contains both the verb phrases "is one of"
and "is not one of".
The latter creates a formula line like
not ({YourTable.YourField} in ["Dog", "Cat"])
Malcolm

 

[hsvb]

Malcolm, this does not give me what I want. Let me give you an example:

Data Field called Treatment can contain one or many of the following: Vaccine, Fecal, Bath, Treatment, etc. I only want to see those records that DO NOT CONTAIN "Vaccine" and in addition, I don't want to see the records that contain Fecal, Bath, Treatment, etc. In other words, I only want the records that are Blank for the word vaccine and while this data field may have other values in treatment, I don't want to see those records nor do I want them counted. It is a null for vaccine, but don't show me anything else.

 

[kenhamady]

Your question was clearer until you started talking about blank and null. Let me try:

You have a text field that contains a list of words, and you have a target list of words to use as criteria. You want to exclude any record whose text field has any one of the target words. Is that right?

If so the following record selection formula would work, using your list of words and your field name:

Not (
"Vaccine" in {field} or
"Fecal" in {field} or
"Bath" in {field} )
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[kenhamady]

Or, better yet:

Not( {field} like ["*Fecal*", "*Vaccine*", "*Bath*"] Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[hsvb]

Is there any easy way to indicate that you want to look at a field if it is "blank" without listing all the values that are possible in that field?

 

[hsvb]

Tried your formula and still not getting what I need. You have been so patient and helpful, let me try with the business description. In my database I have a field called Treatment that can have several variables (Vaccine, bath, fecal, etc.) When an animal comes in I may give them a Vaccine AND Bath AND Fecal, or just a Vaccine, or just a Bath, etc. (any combination). I want to pull up the records for animals that have never had a vaccine. If they have had a bath, fecal, etc., I don't care and don't want to show that data. I want the report to ignore all the variables in the field treatment except if vaccine is missing and those are the only records I want to see.

 

[dcm]

hsvb: The solution for you problem is to use the LooksLike() function as follows:

LooksLike({Treatment},"*Vaccine*&quot

Select for field = False and you'll get all the Treatment field which do not contain the word Vaccine anywhere in their contents

Hope this helps David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[kenhamady]

You need a completely different approach.

Three steps:
1) Add the following formulula to your report, on the detail band:
If "Vaccinne" in {field} then 1 else 0

2) Create a subtotal of this field by pet.

3) Highlight this subtotal and go into the select expert, adding a rule that says count of {@formula} = 0.

If you have more than one table, make sure that the link from pet to visit is an outer join. You don't want to skip pets with no visits.

Any grand totals will need to be running totals to be accurate.

last x years" makes it even harder. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[kenhamady]

You can ignore the last line of the post about x years. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)