PROBLEM WITH CALCULATIONS

[smithbr]

Hello,
I have a query that performs two calculations.
First, it takes the 'InvoiceTotal-AllowanceDeductions'as Commissionable Total
Second, it takes the difference of the above calculation and multiplies it by a commission rate. The problem I am running into is that, more often than not, there is no allowance deduction which makes the Commissionable Total blank because it can not find the difference of the Invoice Total and a blank field. I know I can solve this problem if i create a couple new queries and have it combine all invoices from those with deductions and those without deductions but I am trying to avoid this.
I know there has to be a way to make the allowance deduction field zero if it is null. I tried to run an update query where it would update the table field "allowancetotal". Here is what I out in the query: Update To: [allowancetotal]="0", criteria: is null

Is there a way I can set up the calculation to ignore the allowance total if it is null so that it will return the original invoice total instead of giving me a blank field?

Any suggestions on who I can get this to work...I am open to any way possible...Thanks

 

[Craig9999]

Hi smithBr,

As it's 23:56 in the UK. I could be in need of sleep. But to try and help, but here goes.

Commissionable Total: [Balance]-Nz([DeductionAllowed],0)

Not to sure of how you can fit this into your code. But the Nz function will replace Null with 0 so as your calc works.