467,858 Members | 1,607 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,858 developers. It's quick & easy.

Report/Subreport Problem

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!
Jun 19 '07 #1
  • viewed: 2128
Share:
3 Replies
MMcCarthy
Expert Mod 8TB
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)
Jun 20 '07 #2
dima69
Expert 100+
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.
Jun 20 '07 #3
MMcCarthy
Expert Mod 8TB
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.
Jun 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Steve Edwards | last post: by
6 posts views Thread by David B | last post: by
4 posts views Thread by Mark Reed | last post: by
7 posts views Thread by Ellen Manning | last post: by
4 posts views Thread by Trevor Best | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.