QTony
asked on
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
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
ASKER
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
{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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I actually tried that earlier but it didn't work.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Glad i could help
mlmcc
mlmcc
What do you print if {Parts.Length} is not null?
Is {Parts.Length} text?
Pete