Assist with Totals Control on Form

[bikerted]

I have a control at the foot of my continuous form which is based on a rather complicated query:

SELECT Receipts.Book, Receipts.Receipt, Receipts.[Payee ID], [Payee Details].Payee, [Payee Details].[First Name], Receipts.Date, Receipts.Service, Receipts.Location, Receipts.[Method of Payment], Receipts.Fund, Receipts.IncomeType, Receipts.Amount, Receipts.[Gift Aid], IIf([Gift Aid]="Yes",[Amount]*0.28,0) AS Expr1, Receipts.Comments, Receipts.PayingInNo, Receipts.[Date Paid In], IIf([Method of Payment]="CAF",[Amount],0) AS Expr2, IIf([Method of Payment]="Credit Card",[Amount],0) AS Expr3, IIf([IncomeType]="Invoiced",[Amount],0) AS Expr4, IIf([Receipt]=0,[Amount],0) AS Expr5, IIf([Date Paid In] Is Not Null,0,1) AS Expr6, Receipts.ServiceUserID, Receipts.ReceiptID
FROM [Payee Details] INNER JOIN Receipts ON [Payee Details].[Payee ID] = Receipts.[Payee ID]
WHERE (((Receipts.Book)=[Forms]![SearchesEdits]![Combo76])) OR (((Receipts.Receipt)=[Forms]![SearchesEdits]![Combo97])) OR (((Receipts.Date) Between ([Forms]![SearchesEdits]![Combo99]) And ([Forms]![SearchesEdits]![Combo176]))) OR (((Receipts.Service)=[Forms]![SearchesEdits]![Combo101])) OR (((Receipts.Location)=[Forms]![SearchesEdits]![Combo103])) OR (((Receipts.[Method of Payment])=[Forms]![SearchesEdits]![Combo105])) OR (((Receipts.Fund)=[Forms]![SearchesEdits]![Combo107])) OR (((Receipts.IncomeType)=[Forms]![SearchesEdits]![Combo109])) OR (((Receipts.Amount)=[Forms]![SearchesEdits]![Combo111])) OR (((Receipts.[Gift Aid])=[Forms]![SearchesEdits]![Combo113])) OR (((Receipts.Comments)=[Forms]![SearchesEdits]![Combo115])) OR (((Receipts.PayingInNo)=[Forms]![SearchesEdits]![Combo117])) OR (((Receipts.ServiceUserID)=[Forms]![SearchesEdits]![Combo180])) OR (((Receipts.[Date Paid In])=[Forms]![SearchesEdits]![Combo119])) OR (((Receipts.[Payee ID])=[Forms]![SearchesEdits]![Combo73]))
ORDER BY Receipts.Book, Receipts.Receipt;

All this enables me to do searches on several combo boxes and "filter" receipt data out, which in turn gives me varying totals below. When I come to pay in monies, I open a pop-up form and, via an update query, assign a PayingInNo to each line that meets the correct criteria - hence I have placed various expressions in there to exclude those lines from paying in. All this works fine in the updating process, but when I want to show the value 0.00 for sums to be paid in, after the updating process, I run into difficulty. Here's the expression in the "Total to Paid In" control (where "Amount" represents total monies on the form and all the expressions from the query are IIf expressions for not-paying-in criteria):

=NZ(Sum(([Amount])-([Expr2]+[Expr3]+[Expr4]+[Expr5])))*IIf([PayingInNo] Is Null,1,0)

This works ok sometimes. I can't understand how it can vary and would greatly appreciate some assistance.

Many thanks,

Ted.

 

[CosmoKramer]

Ted,

Is it possible that Expr2-5 could be null?? If so, try wrapping an Nz around them too:

=NZ(Sum(([Amount])-(Nz([Expr2])+Nz([Expr3])+Nz([Expr4])+Nz([Expr5]))))*IIf([PayingInNo] Is Null,1,0)

Si hoc legere scis, nimis eruditionis habes

 

[bikerted]

Thanks CosmoKramer for your reply, but I tried it and still the result was not consistent. I even tried various other expressions including:

=NZ(Sum(([Amount])-(NZ([Expr2])+NZ([Expr3])+NZ([Expr4])+NZ([Expr5]))))*NZ(IIf([PayingInNo] Is Null,1,0))

but no joy! What is perplexing is how it's always zero result when every record is showing even though this includes those records that consistently allow the error, and display an amount.

Perhaps the problem is that my PayingInNo is in this format: 03500487, where there is always a leading zero? In the underlying table its a number format but maybe here it is treated as text. I'm rather confused.

Any other thoughts/suggestions would be greatly appreciated.

Thanks,

Ted.

 

[bikerted]

I've noticed something, but it confuses me more!

When I click one of the drop-down arrows of fields in the selected records where the error occurs, the Total Paid In goes to zero and subsequently, reselecting the records, it reverts to a total more than zero!

I'm sure I read something somewhere about a certain function causing inconsistent results. Does anyone have an inkling, please?