Subtotals

[jonp]

Hi there!

Hopefully someone can help me with this issue I have been kicking around the past few days.

I have a report that contains 3 subreports. Each subreport has a hidden footer that sums a control. In the footer of the master report there is a control that is calculating simple arithmetic from the 3 other subreports. The function works great. However, if there is a null value in one of the subreports I receive an error. I know that I need to somehow place the Nx function somewhere to change the null value to a zero string. I have searched the help for null values and it gives me examples of this function but I am not certain where to place it.

Can someone give me some direction?

Thanks in advance!

jonp

 

[Fornatian]

In the control source property of the control where it says the field name put the Nz function so instead of:

=[My Control]

use

=Nz([My Control],0)

The better way to prevent this is set the default property for the control to zero.

Ian

 

[jonp]

Thanks Fornatian for the Nz function help. The function seems to work great but I found a problem concerning the query that generates totals for the master report. Like I mentioned earlier on this topic, there are 3 subreports with their own queries. The subreports have a footer that sums a control. If there is not any information to sum, the footer displays ERROR. I tried placing the Nz function on this control but it does not change the result. I even tried an IIF statement, but still no luck

Any ideas on that?

Cheers,
JONP

 

[Fornatian]

Could this be because the name of the control with the Nz function in is the same as the name of the field which was causing your problem? If so I think Access will still try and sum the control using the field as the source. Try something like,

=sum(iif(isnull([ControlName]),0,[ControlName]))

Should sum zero's for nulls and the controls where there is a figure.

Comeback if you still have trouble.

Ian



[This message has been edited by Fornatian (edited 04-18-2001).]