I Are Dumb

[rowyn]

Or maybe it's Crystal Reports, not me

Comments

[sebkha]

Crazy thought: try not(Funfield = Foo).

It might work!

[rowyn]

The actual construction for the formula I'm using is something like:

{funfield} not in [70,80,90]

I'm not sure offand if Crystal has another construction for "not" but I'll take a look when I'm at work tomorrow. Can't hurt.

[terrycloth]

Can you specifically include blanks somehow maybe?

I Are Not Reel Programr

[rowyn]

I shouldn't've used Foo for my example, because funfield isn't actually a string field -- it's numeric. With some (but not all!) string fields that are null in the database, using something like String ="" will work.

But with this numeric field, if I look at the null fields in the actual database, they display as blank, not zero. However, in Crystal's report they show as 0:

Name Funfield
Boo 10
Goo 2
Joo 5
Noo 0

Funfield = 2 would get me:

Name Funfield
Goo 2

But Funfield = 0 just gives:

Name Funfield

No results. >.

Re: I Are Not Reel Programr

[terrycloth]

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.

[the_vulture]

'Master Reset' - That's what I'd do if it was a cellphone. Seriously, it looks like a bug.

[postrodent]

Whooa, time snap! Would you believe that one of my very first jobs was writing and formatting Crystal Reports, in 1995 or so? My mom got me the job, since I'd never seen it before then -- rather naked nepotism. Anyway, I can't help you, but I do sympathize.

[telnar]

If you can use raw SQL to generate the report (perhaps by editing the SQL generated by Cyrstal before using it), you could probably fix the incorrect handling of the NULL value. Is sounds like what you want (in ANSI SQL) is:

Select ... From ...
Where (Funfield is NULL) or (FunField not in (70, 80, 90))

and Crystal is mangling the interpretation of that expression.

I've never used Crystal Reports, but if there's a way to see what SQL it is generating to retrieve the data, you can better understand the nature of the bug.