Hello,

Hopefully I can get some ideas from the group on ways to tackle this issue.

I have files, each with a FileID. There can be 1 to many rows for each FileID.  Each Row has an Amount Field.  For some rows there is an actual $$ amount other rows the Amount is NULL.  On Rows that have NULL, if it is a Unique FileID then I need to replace NULL with 600.00 if the File ID is not Unique I need to replace with 0.00 or leave NULL. 

Here is a sample of the Original Data.

FileID      Amount
36             687.54
36             0.00
430           522.00
430           0.00
1858         0.00
1873         0.00
5507         189.12
5507         378.22
5507         0.00

I need the data to look like this.


FileID      Amount
36             687.54
36             0.00
430           522.00
430           0.00
1858         600.00
1873         600.00
5507         189.12
5507         378.22
5507         0.00

This Data is all in the details section and I will be sorting by FileID. 

The final Piece to this puzzle is I need to be able to sum the New Amounts in Report Footer for a grand total.

Any Ideas On how I can tackle this issue?

Thanks for any help you can provide.

Thanks for the Input Dblank.

I have tried something similar to what you have proposed.  The issue here is I have both instances.  Where there is only one record of a File ID and the Amount is NULL and 2 or more records for a File ID where an amount is NULL

In order to get the Data to display the way I want, I created a formula to do this (I should have shown this in my original post)

This is that Formula
***************
If OnFirstRecord then (If isnull({vw_MF_All_2010_Liable_Files.Amount}) then 600.00 else
If {vw_MF_All_2010_Liable_Files.Amount} = 0.00 then 600.00 else
{vw_MF_All_2010_Liable_Files.Amount})


 else
If ({vw_MF_All_2010_Liable_Files.Liable_Type} = "zNo Pending Trans"
and Previous({vw_MF_All_2010_Liable_Files.FileID}) = {vw_MF_All_2010_Liable_Files.FileID}) then 0.00

else

(If isnull({vw_MF_All_2010_Liable_Files.Amount}) then 600.00 else
If {vw_MF_All_2010_Liable_Files.Amount} = 0.00 then 600.00 else
{vw_MF_All_2010_Liable_Files.Amount})
**************

This will display the correct Amount for all Amount fields that have NULL or 0.00.

But I cannot sum this field, so I was hoping someone had another idea on how to do it display the data and sum it.

Ahh...I have better Idea of what you were getting at.  It was an option I had explored briefly earlier today but your approach has given me a different way to look at it.  I will look into it further tomorrow when I have fresh eyes. 

I appreciate the help.  I have run into this type of issue before and have never been able to solve it.

I'll let you know how it goes.

Can you post a little wider scope of some sample data with ALL of the rules of exclusion and inclusion of rows for a total Sum?

OK. There may be a more elegant solution using variables but the shared variable process would require using sub reports and that is a no go for you.