I'm pulling a numeric column into my report, and all I want to do is flag the field if the value is NULL in the DB, ideally by making the background yellow. There doesn't appear to be a way to handle NULLs in the Highlighting Expert popup. OK, so I use the Formatting Editor, Border tab and Background script button, and I put in this:
If Not ISNULL(CurrentFieldValue) Then
If CurrentFieldValue < 0 Then
crGreen
Else
If CurrentFieldValue > 10 Then
crRed
Else
crYellow
Else
crYellow
Crystal tells me there's an error with that: "A field is required here", meaning that it doesn't see the built-in CurrentFieldValue function as a field, so I can't check it for NULL. Really?? That's going to make it quite tedious to do for all 20-some columns I've got to do this for, but ... OK, so let's tell it the name of the field:
If Not ISNULL({MyField}) Then
If {MyField} < 0 Then
crGreen
Else
If {MyField} > 10 Then
crRed
Else
crYellow
Else
crYellow
No errors, but no formatting either when the field is NULL. Apparently it knows enough to leave space for NULL fields, but is otherwise not smart enough to do anything about them. So I learned that you can change the Report Options to default NULL values to the default for that field type, and that gets me the formatting results, but also gives me phony values throughout my report--zeroes where there aren't really zeroes. And no, I can't filter out zeroes, because zero is a real value in my data that I don't want to lose.
The only solution I've seen (and not yet tried, because it seems utterly ridiculous to have to do) is to create a new formula field (for each of my 20-some columns), and in it check for a NULL value in this field, and if it's NULL, assign some dummy value that I have to choose (as if any value other than NULL is really appropriate for this ... negative infinity? doubleplusungood?). Then in my formatting logic, I'd check for that dummy value and set my formatting, and also add additional formatting to the font so it matches the background color, and therefore appears empty.
There's got to a better solution to this very basic, and surely common problem. What am I missing?