Format font color 2

[jenschmidt]

I have a very simple report with two groups (none hidden) and some formulas and subtotals.

One of the formulas in the details section has this font color formula applied to itself:
if {@Check In to Arrival to Deletion}>19 then crred
else crblack

The formula, {@Check In to Arrival to Deletion}, is:
datediff("n",{PAT_ENC.CHECKIN_TIME}, {PAT_ENC.ARVL_LST_DL_TIME})

In regard to the color change, everything works perfectly until the formula returns a value of zero. If the record previous is >19, the zero turns red as well. I even have the following case:

27 (red)
0 (red) -- should be black
0 (red) -- should be black
33 (red)
3 (black)
0 (black)
16 (black)
4 (black)

Any ideas on what I'm doing wrong? Have I posted enough info?
Thanks!

jennifer.giemza@uwmf.wisc.edu

 

[synapsevampire]

Oddly enough, it sounds like a null is involved, so try:

if isnull({@Check In to Arrival to Deletion})
or
{@Check In to Arrival to Deletion} <= 19 then
crblack
else
crred

-k

 

[jenschmidt]

Thanks for the feedback! I pasted your formula over the one I had, refreshed the data and it still is giving me the same problem. I'm totally baffled!

Thanks again for the help!

jennifer.giemza@uwmf.wisc.edu

 

[rnd]

Else NoColor could work better than crBlack. You can try.

 

[jenschmidt]

Yikes - now I'm even more confused! When replaced "else crBlack" with "else NoColor", I lost all the color, even the red that was correct.

I think this is the report telling me to go home early!!

jennifer.giemza@uwmf.wisc.edu

 

[johnstrang]

Jennifer,

Are the values returned by your formula
{@Check In to Arrival to Deletion} correct?

For debugging purposes it might be an idea to include the values of {PAT_ENC.CHECKIN_TIME} and {PAT_ENC.ARVL_LST_DL_TIME}) just so you can confirm that the formula is working as you expect.

John

 

[synapsevampire]

If you were getting red before, and now you're not, politely close Crystal and find a joint with a martini

-k

 

[jenschmidt]

The calculations are correct, but it returns the zero when the two fields, {PAT_ENC.CHECKIN_TIME} and {PAT_ENC.ARVL_LST_DL_TIME}, are blank. I was going to try converting the fields to fill in a zero using:

if isnull({PAT_ENC.CHECKIN_TIME}) then 0 else {PAT_ENC.CHECKIN_TIME}

but it returns the error that a number is required after the else. Can't turn it into a number because it's a datetime field. Now what?

Thanks again everyone for your help!

jennifer.giemza@uwmf.wisc.edu

 

[tektipdjango]

The formula, {@Check In to Arrival to Deletion} should be:

if (isnull({PAT_ENC.CHECKIN_TIME}) or isnull({PAT_ENC.ARVL_LST_DL_TIME})) then 0 else datediff("n",{PAT_ENC.CHECKIN_TIME}, {PAT_ENC.ARVL_LST_DL_TIME})

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[jenschmidt]

Thank you django!! It worked wonderfully! Thank you to everyone for helping on this issue! I really appreciate it!

jennifer.giemza@uwmf.wisc.edu