8.5: Unable to combine IsNull with another "IF" condition

I'm having a roadblock with what I thought should be a simple two part formula  in combining IsNull with another condition. Here's my problem:

I use Crystal to organize cabinets for a cabinet making company.  Under normal conditions, there are certain cabinets parts present or not depending on the cabinet constuction. When there are no parts present, nothing is printed for that entry. There are times however, when there are no parts present but if a cabinet has a distinctive name then what  we would like is to have a text line entered.

What's confusing for me is that if I try either of the conditions alone i.e The Null value or the distinctive name, I can get my text line. But if I try to combine them I can't.

Here's what I mean:

If {Cabinets.Name} = "Distinctive Name" then "Text Line"                     (This works)

If IsNull ({Parts.Length}) then "Text line"                (This works)

If {Cabinets.Name} = "Distinctive Name" and  IsNull ({Parts.Length}) then "Text Line"                     (This doesn't work)  I also tried reversing the line:

If IsNull ({Parts.Length} and {Cabinets.Name} = "Distinctive Name" then "Text Line"                        (But it didn't work either)  I also tried additional parenthesis:

If  ({Cabinets.Name} = "Distinctive Name")   and    (IsNull ({Parts.Length})) then "Text Line"            (But no luck again)

(On all of these, I did use the formula "check" feature which returned the "No errors found" message.


I'm puzzled because I do many of these types of combinations (although this is my first with the IsNull function) that are far more complex than this one.
I'm assuming that there is something distinctive about the IsNull feature that I'm not aware of.  

any advice would be greatly appreciated---Thanks
QTonyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Hi QTony,

What do you print if {Parts.Length} is not null?

Is {Parts.Length} text?


Pete
QTonyAuthor Commented:
Thanks for responding Pete, to answer your question:


{Parts.Length} is a numeric field. It holds the decimal descriptions of the cabinet parts. Thus when the field is not null, something to the effect of

"4.5"

 would be printed.

I also have access to the equivalent string field which is {Parts.Length String} It holds values in fractions such as "4 1/2" I can use either one for my selection process. I tried the IsNull formula I listed above on both string and numeric but I had the same problem with both of them
QTonyAuthor Commented:
One thing I thought I should add is this: When I tested the above example(s), I confirmed that the field was Null before I began. This is what's confusing me; is why both conditions are functioning independently but I cannot combine them with a simple "and" In other words, without making any changes and using the exact same information,


I can run this line successfully:                If {Cabinets.Name} = "Distinctive Name" then "Text Line"  


and I can run this line successfully:          If IsNull ({Parts.Width)}  then  "Text Line"  

 

but I can't combine them



Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

dosthCommented:
Hi

Create two formuala field, assing the DB values to the formula and try this

if (({@F1} = "Distinctive Name") and IsNull ({@F2}))  then "New Text"

Thanks
Dosth
QTonyAuthor Commented:
I actually tried that earlier but it didn't work.
dosthCommented:
Hi

if (({@F1} = "Distinctive Name") and (IsNull ({@F2}) or {@F2} <> '')  then "New Text"

Thanks
Dosth
Mike McCrackenSenior ConsultantCommented:
Try it this way

 If {Cabinets.Name} = "Distinctive Name" then
     If IsNull ({Parts.Width)}  then  
         "Text Line"  

What do you want displayed on a false?

This is a particular problem I have seen with Crystal.  Boolean expressions involving an AND sometimes don't seem to work.  I have not found anything that pinpoints the problem or what might make it happen.

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QTonyAuthor Commented:
I actually tried that already. In fact, I tried quite a few things such as re-setting the Null field to default and using "0" nstead of Null and assorted permutations on what should be a very simple boolean but I haven't been able to make it work. I've been able to work around the problem though by using a variation on your earlier advice by creating a subreport and having it always print the text line and then adding conditional formatting. Due to the fact that a only a  small volume of records are generated by my client, (under 1,000) the additional report doesn't appear to have an impact

One interesting thing regarding this matter is that last night I went into Crystal's help files and under" Null"  values one of the help advisements stated that when Crystal encounters a Null value it stops processing the formula. The exact wording was:


     "In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value."
 
Another advisement was this:

     "In other words, Crystal Reports will stop evaluating a Boolean expression when it can deduce the results of the whole expression."

I'm speculating that my problem is somewhere in here; maybe Crystal just stops cold when it encounters the Null values in the way that I've set them up as booleans. I probably need to approach it differently but since I have cobbled together a solution I think I'll wait until another time to address it. ---Thanks


Also, If you can, (or anyone else)  I'd appreciate some advice on this point question: What is the procedure in awarding points in this type of instance when I've decided to take a different tack?  None of the answers really "worked" per say and when I went to the "commonly asked questions" I didn't see anything that pertains to this. My instinct is to proportionally split the points between yourself and dosth for spending the time to submit formulas but I'm not sure if that's the right protocal

Thanks again
Mike McCrackenSenior ConsultantCommented:
Glad i could help

mlmcc
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.