Crystal Reports 9 - Formula if

if mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"
 OR {sp_cstTruckSched1;1.aoTruck}="1"
   then "GROUP 1"
else
  "GROUP 2"

IN your formula both mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc"  AND {sp_cstTruckSched1;1.aoTruck}="1"  have to be true.

DO you have data that meets both criteria?

If not then perhaps changing the AND to OR as suggested is what you want.

mlmcc

mlmccc

I was under the impression that both did not have to be true otherwise i would have put
but I could be wrong

Both have to be true.  And yes, I had records that met that criteria.  The report grouped them correctly, but for some reason the "GROUP 1" heading name shows up as blank.  The field exists, but it's blank.  It's very strange.

Do you get values but the group name is blank?

mlmcc

Can you post a screen shot of the formula?

mlmcc

Hey James,

Yes, I found that null was the issue.  I don't understand why that would make the heading blank but would still group properly, but at least it works.  

Thanks all ...

It makes the heading blank because the formula doesn't return a value.  THe only thing you can do with a NULL value is test if it is NULL.

If you try any other comparison in a Crystal formula, Crystal terminates execution and the result is not predictable.  In your case it must be returning nothing.

mlmcc

mlmcc,

 The part that doesn't make sense to me is that if he has records where aoTruck = "1", then it seems like he should have gotten "GROUP 1" for those records.  IOW, there would be 3 groups.

 mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc" and aoTruck is null
 Group name is null

 mid({sp_cstTruckSched1;1.aoBudgClas},1,3)="abc" and aoTruck = "1"
 GROUP 1

 Everything else
 GROUP 2


 James

We only need two groups.  Group one is 'abc' and truck='1'.  Everything else falls into group 2.  

It groups correctly, but no value shows up in the group header #1.  when I add and (truck='1' and not isnull(truck)), it works.

at least it's working, even though it doesn't make much sense.  :)

thanks.

I know that you only wanted two groups.  It just seemed to me like nulls in truck would add a third group, where the formula produces a "null" result because truck is null.  I would have thought that "Group 1" and "Group 2" would still show up when truck was not null.  But, like I said, it seems like sometimes the problems caused by nulls are not as obvious, at least to me.

 FYI, the IsNull test really should be first, _before_ you do anything else with the field.  Or another option would be to go to File > "Report Options" and check the "Convert database null values to default" option (assuming that CR 9 has that option).  Then any "normal" formulas in the report will just see a space or whatever (depending on the type of field), instead of a null.

 FWIW, here's a quote from the CR 10 Help:

In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.