Checking to see if 2 fields are Null 1

[MikeVac]

I am creating a report to list records that are active, closed , overdue or active with no due date. I have formula's set up for these. The formula to check for records with no due date that is not closed is as follows:

If IsNull ({CCM_MASTER_1_ACT.DUE}) and Isnull ({CCM_MASTER_1_ACT.CLOSED}) Then
1 else
0

I can then sum these and hide the details. The problem I am getting a 1 when only one of these fields are null. Does anyone know of a better way to do this? Thank You.

 

[lbass]

Your formula is fine, but I think you need to look at the records your report returns. I'm guessing you are looking at account IDs or something like that and that you want to show those IDs that have no due date and are not closed. Try removing your suppression formula and looking at your detail level data. You might find multiple records per ID where at least one of the records meets your criterion, while others don't.

For further help, I think you need to supply some sample data at the detail level that shows the possible variations in records per ID.

-LB

 

[MikeVac]

Thanks for your response here is more detail.
My report looks something like this (With details not suppressed)

(Received) (Act w/Due) (Act wo/ Due) (OverDue) (Closed)
123456 0 1 0 0
456123 1 0 0 0
789456 0 0 0 1
568956 1 1 0 0

I am using formulas to calculate columns 2,3,4 and 4. Then I am just summing them to get results. The only problem I am having is in the last row where there is a 1 in the second and third column (there should only be a 1 in one column per row)
It seems that if one of the fields is null it returns 1, instead of having to have both fields null (Formula in earlier post)
I am using CR 8.5 - connecting to Oracle.
I would rather not convert null's to a default value as it would affect other reports.
I hope this is clearer. Thanks

 

[dgillz]

Try enclosing both tests within a set of parens as follows:

If (IsNull ({CCM_MASTER_1_ACT.DUE}) and Isnull ({CCM_MASTER_1_ACT.CLOSED})) Then 1 else 0

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[MikeVac]

Thanks DGILLZ - I tried that before and it didn't change anything. I pasted in your answer in case I had made a mistake but it is still giving me the same results. It seems like if one of the fields is null it automatically returns a 1.

 

[kskid]

Please post the other formulas

 

[dgillz]

Try creating 2 boolean formulas, then placing them in the details section to see what they say:

Formula1
IsNull({CCM_MASTER_1_ACT.DUE})

Formula2
IsNull({CCM_MASTER_1_ACT.CLOSED})




Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[kskid]

Is there a separate field that provides the status of the records you mentioned (i.e., received, active, overdue, and closed) or is it based on how the date fields are populated.

 

[MikeVac]

Thank You dgillz - that was a great idea - I added the temporary formulas so I could closely analyze the true's and false's. I found an error in one of the formulas where it was < and it should have been >. Also, when I was changing the order of my columns - I had not moved 2 of the details, only the header which really made it confusing. This is my first time posting a question to something like this - I'm impressed with the response. I have tested it and it is right on now.
Thanks to lbass and wichitakid for your responses also.
Mike from PEI Canada