Grouping by formula containing date - impossible ???

[virgilash]

Hi everybody!

I'm using Crystal Reports 10 Pro Edition.

I have defined a formula :

f = if not isnull(table1.field1) then table1.field1 else table2.field2

After a few pages where this formula really seems to work, at some point it stops working, from that point C.R. doesn't even sort properly the records even the GH1 is = {@f} and sorting criteria 1 = {@f}

table1, table2 are tables in a SQL Server 2000 database, they are properly connected, I really don't understand what's going on .... :-(( Any help is more than welcome ...

 

[IanWaterman]

Have you tried doing it the other way round. In Crystal you MUST tell it what to do with NULLS first

f = if isnull(table1.field1) then table2.field2 else table1.field1

Ian

 

[virgilash]

Yes, I've tried ... Still not working ... :-(

 

[lbass]

I wonder what you are trying to achieve overall? Can field2 also be null? You might want to layout your two fields in the detail section and see how they relate to each other and it might clarify whether the formula makes sense. Maybe show us a sample, too.

-LB

 

[lbass]

PS--your thread title references a date, but your post doesn't--where does that fit in?

-LB

 

[virgilash]

Sorry lbass, I should have called it "Grouping by formula containing date fields", those two fields (I mean table1.field1 and table2.field2) are date type fields.

And this is what I want to do:

I have a table containing cheques - I have to group them by their payment month, and the cheques that haven't been paid yet (having that test1.field1 = NULL) have to be grouped by another date (date when those cheques have been introduced - table2.field2). This second field can't be NULL.
And a solution like G1 = month(table1.field1) and G2= month(table2.field2) is not acceptable (I mean my boss doesn't accept it - the cheques paid this month should appear together (in the same group) with the cheques introduced this month ...

virgil

 

[lbass]

If {table2.field2} is always populated, and {table1.field1} is not, then you should have a left join FROM table2 TO table1 on the cheque field. Then use a formula like this for grouping:

if isnull({table1.field1}) or
{table.field1} = date(0,0,0) then
{table2.field2} else
{table1.field1}

-LB