Invoice Report not adding totasl unless all fields are filled in.

[Analogkid]

I have an Invoice form that when I run it, it does not give the total unless I have all fields filled in. The totals come from 3 different queries from a form. But when I run the report, it gives me a #error on the blank totals and will not fill in the Grand total unless all total fields are filled in. See screenshot. What is the best way to fix this? I have the totals in the footer with an expression of =[Mat_invoice].[Report]![SumOfMaterialTotal]
The Grand Total expression is =[Emp_invoice subreport].[Report]![SumOfEmpTotal]+[Equip_invoice subreport].[Report]![SumOfEquipTotal]+[Mat_invoice].[Report]![SumOfMaterialTotal]
Is there a way to default the empty fields as 0 so the total can be filled? The grand total works if all of the fields are filled in.
Thank you for your time.

Attachment 7758

[pbaldy]

Haven't looked at the attachment, but this sounds like what you need:

nnz function

[June7]

The attachment link isn't working anyway.

[Analogkid]

Where would I put the nnz function? Here is a sample of my DB. Pick a code and selecte a user with total hours. Then, run the Invoice report.

[pbaldy]

Anyplace you're referring to the subreport:

=[Emp_invoice subreport].[Report]![SumOfEmpTotal]

you would use the function:

=nnz([Emp_invoice subreport].[Report]![SumOfEmpTotal])

[June7]

Except I think the function is Nz not nnz.

If the value argument is not used what is returned depends on where the Nz is used http://office.microsoft.com/en-us/ac...001228890.aspx but can return any specified value. As example:

=Nz([Emp_invoice subreport].[Report]![SumOfEmpTotal], 0)

[pbaldy]

Except I think the function is Nz not nnz.

No, read the link.

[June7]

Oh, thanks Paul, I see you created a custom function.

I have used the intrinsic Nz function extensively and encountered issue with it only with Excel VBA. I think the Nz is Access specific.

[pbaldy]

Well, I didn't create the custom function, Keri Hardwick did,and the built-in Nz() would not work in this situation anyway.

[June7]

Finally looked at the project and read the link more carefully and understand issue better. Yes, I can see that Nz not applicable. I just never had to build reports like this so didn't immediately recognize what was happening.

Can use the function call or an IIf expression in the textbox:
=IIf(IsError([Mat_invoice].[Report]![SumOfMaterialTotal]),0,[Mat_invoice].[Report]![SumOfMaterialTotal])
=IIf(IsNumeric([Mat_invoice].[Report]![SumOfMaterialTotal]),[Mat_invoice].[Report]![SumOfMaterialTotal],0)

Then the GrandTotal expression would be: =[EmpTotal]+[EquipTotal]+[MatTotal]

[Analogkid]

Can use the function call or an IIf expression in the textbox:
=IIf(IsError([Mat_invoice].[Report]![SumOfMaterialTotal]),0,[Mat_invoice].[Report]![SumOfMaterialTotal])
=IIf(IsNumeric([Mat_invoice].[Report]![SumOfMaterialTotal]),[Mat_invoice].[Report]![SumOfMaterialTotal],0)

Then the GrandTotal expression would be: =[EmpTotal]+[EquipTotal]+[MatTotal]

I tried this but now, the results come out $1.00. Even if I enter something in, it remains $1.00

Thank you for all of your time with this.

[pbaldy]

I'm too lazy to type all that out for every formula, but the nnz() works as advertised.

[June7]

I tested with the posted project and the IIf's also worked.

[Analogkid]

June7 - You are a genius. Thanks for all of your help. The IIFs worked perfectly.
Thanks pbaldy for your suggestions. I have found the IIfs function work the best.
Now, ONE more thing- FIFO inventory. But that will be a different thread.
BTW- How does my DB look as for functionality? I mean, do you see any potential issues with the way I have the structure or tables/queries etc??
Thanks again for all of your help

[June7]

Structure looks good to me.

Do have a few objects with spaces in names.

Used Name as fieldname in WO_Customer and Name is a reserved word.