I Are Dumb

[rowyn]

Or maybe it's Crystal Reports, not me.

I run into this problem now and again, where I need to do a report on a database field that might or might not have actual data in it.

As an example, starting with a report that shows the value of Funfield in all records:

Name Funfield
Boo Foo
Goo Ooo
Joo Due
Noo

Now I modify this report to show all records where Funfield = Foo.

Name Funfield
Boo Foo

Next, show all records where Funfield != Foo.

Name Funfield
Goo Ooo
Joo Due

Why isn't Noo on this report?

If I try isnull({funfield}), I get no results:

Name Funfield

Now, say I do a formula named "NoFun" and define it as "if {funfield} = Foo then true, else false" and display it on my original all-results report, I get:

Name Funfield Nofun
Boo Foo True
Goo Ooo False
Joo Due False
Noo False

But if I try to get sneaky and ask for all results where Nofun is False:

Name Funfield Nofun
Goo Ooo False
Joo Due False

... Noo's still missing.

Anyone out there smarter than me that knows (a) why this happens and/or (b) how I can get around this? I'm using Crystal v.11.

... and preferably short of redoing the report in Excel, because it's not this simple a report. ;_;

Comments

Re: I Are Not Reel Programr

[terrycloth.livejournal.com]

Right -- a blank cell is different than a cell with value 0 or value '0 length string'. The underlying database engine probably has special blank-handling formulas, maybe Crystal Reports does too?

Re: I Are Not Reel Programr

[rowyn]

Crystal has "isnull" but isnull({funfield}) doesn't get any results either.

Crystal also has a toggle for "force NULL values to 0", I think. I can try playing with that and isnull and =0 in various configurations, and see if I get anything.

Re: I Are Not Reel Programr

[bburnell.livejournal.com]

If I remember correctly, you have to check a bunch of things in Crystal, like isnull and len(trim(x)) = 0, etc...