Sub Form/Main Form Sub Totals

[danielw.au]

Hi Everyone,

I have a database that tracks workorders and then the associated invoices that result froma workorder.

The process is that the user creates a workorder and when an invoice related to the workorder comes in, they register the invoice and select the workorder it belongs to.

I have a form (DFRM_PayrollWorkorders) that allows the user to display the workorder. This form contains a sub form DFRM_PayrollInvoices_Summary that displays any invoices that have been registered against the workorder id. Sample screen attached.

• In the sub form, I have added two expressions , =Count([InvoiceNumber]) and =Sum([InvoiceValueTotal]).

• In the work order form I have added two expressions, =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Count] and =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Sum].

The above simply displays the total invoice count and total invoice amount for any invoices associated with the work order being dsiplayed on screen.

My issue is, whenever there are no invoices for the work order, I get #Error in the =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Count] and =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Sum] expressions.

Ideally, I'd just like the count and sum expressions to show blank or 0. Any assistance would be great.

Thank you in advance.

Daniel

 

[Galaxiom]

=IIF(IsError([DTBL_PayrollInvoices_Summary].[Form]![Invoice_Count]),Null,[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Count])

 

[danielw.au]

Thank you, awesome. Works great.