Report Calculation - Access 2003

[Grey Whipple]

I have inherited an Access DB that accumulates 'hours worked' by name and date. I also inherited a cross-tab query that displays monthly totals by name and month. Each row is for a different person and each column is for a different month.

I use the Report Wizard to create a report that reads the results of the cross-tab query; and the data is displayed correctly, an array of hours by name and month. In the Report I wish to sum the columns vertically (total hours for each month) and sum the rows horizontally (total hours by name).

The problem is null values where no hours are reported in a particular month. In the Report I can add the columns using the Sum function, but I can't add the rows because of the null values.

=[01]+[02]+[03}+...+[12] works if there are no nulls, but does not work if there is at least one null in the row of values.

Any ideas?

 

[mhartman]

Convert your "Null" values to zero before adding them

Use the Nz function to do this. Its usage is in the help file.

Mark

 

[Grey Whipple]

Many thanks to Mark for taking the time to respond.

The winning expression is =NZ([01])+NZ([02])+...+NZ([12]).

Grey

 

[mhartman]

Your welcome and good luck with this!

 

[Villarreal68]

Love you guys!

I want to thank Mark and Grey for this post. I was looking for the same type of report and reading this post I had enough information to get it going. And the added bonus of fixing what I was going to encouter, Null values!

Just what i needed when I needed it!

Thanks again to both of you!

René

------

You can't teach anyone anything.. you can only help them find it within them--Galileo (I think)