Report/Subreport Problem

[Connell]

I am using a command button from a form (Access 2000) to produce a report. The form and the report each have a subform (subreport). Here is the expression that produces a total for one field on the report.

=nz([Tech Assist Subreport]![Total Service Hours])+nz([CaseLaw Updates Hours])+nz([DefenseNET Newsletter Hours])+nz([Defense Magazine Hours])+nz([Brief Bank Hours])+nz([Other Publication Hours])+nz([Amicus Hours])+nz([Training Performed Hours])+nz([Training Attended Hours])+nz([Administration Hours])+nz([Collaboration Hours])

This expression works perfectly as long as there is a value for the [Tech Assist Subreport]![Total Service Hours])

If [Tech Assist Subreport]![Total Service Hours]) has no value, that subreport is blank on the report and the total expression results in "Error"

I have also tried this expression:

=IIf(IsNull([Tech Assist Subreport]![Total Service Hours]),nz([CaseLaw Updates Hours])+nz([DefenseNET Newsletter Hours])+nz([Defense Magazine Hours])+nz([Brief Bank Hours])+nz([Other Publication Hours])+nz([Amicus Hours])+nz([Training Performed Hours])+nz([Training Attended Hours])+nz([Administration Hours])+nz([Collaboration Hours],nz([Tech Assist Subreport]![Total Service Hours])+nz([CaseLaw Updates Hours])+nz([DefenseNET Newsletter Hours])+nz([Defense Magazine Hours])+nz([Brief Bank Hours])+nz([Other Publication Hours])+nz([Amicus Hours])+nz([Training Performed Hours])+nz([Training Attended Hours])+nz([Administration Hours])+nz([Collaboration Hours])))

Same error is produced. Any ideas are appreciated!

[MMcCarthy]

You are using the nz function but you are not giving a value to be substituted if null.

the nz function is ...

nz(Expression, value if null)

Try this ...

=nz([Tech Assist Subreport]![Total Service Hours], 0) + nz([CaseLaw Updates Hours], 0) + nz([DefenseNET Newsletter Hours], 0) + nz([Defense Magazine Hours], 0) + nz([Brief Bank Hours], 0) + nz([Other Publication Hours], 0) + nz([Amicus Hours], 0) + nz([Training Performed Hours], 0) + nz([Training Attended Hours], 0) + nz([Administration Hours], 0) + nz([Collaboration Hours], 0)

[dima69]

I'm sorry I have to contradict our Administrator ): but this is not the problem of NZ function.
The problem is that SubReport works differently from SubForm, meaning that when there is no data in subreport, it behaves like it dosn't exist at all (from the point of view of the main report), so any reference to subreport with no data will produce an error.
My solution for this is to create a function in the main form module, wich will return the required value from the subreport.
This function should include error handling, returning 0 on error.

[MMcCarthy]

I'm sorry I have to contradict our Administrator ): but this is not the problem of NZ function.
The problem is that SubReport works differently from SubForm, meaning that when there is no data in subreport, it behaves like it dosn't exist at all (from the point of view of the main report), so any reference to subreport with no data will produce an error.
My solution for this is to create a function in the main form module, wich will return the required value from the subreport.
This function should include error handling, returning 0 on error.

My sincere apologies, I missed this entirely. Must have been tired when I was doing this (LOL).

Dima is of course right, thanks for the pick up.