Consider an empty field $0.00 in a query in order to provide a result in an equation

[dictionary101]

I am working on a project funding query. I've managed to subtotal the invoices posted per project and subtract that amount from the total amount allocated for the project.

REMAINING Funding Available: ([qry_Funding totals AND Invoice totals].[SumOf$Funding]-[Sum Of InvoiceAmount])

The result shows me the amount remaining for any projects from which an invoice has been posted and funds subtracted. Exactly what I want to see.

However, not all projects have had work done yet, so there aren't invoice amounts to subtract. How can I write the equation so that those projects show $0.00 as the invoiced amount and the full allocated amount remaining?



Thank you in advance for your help!

[June7]

Arithmetic with null results in null. Handle null: Nz([Sum Of InvoiceAmount],0)

[dictionary101]

I've never seen Nz before. Where does that fit in the equation: REMAINING Funding Available: ([qry_Funding totals AND Invoice totals].[SumOf$Funding]-[Sum Of InvoiceAmount])?

I tried a couple of places I thought might work and, depending on where I put it, I got error messages about commas or parentheses being wrong.

[June7]

It fits as shown.

REMAINING Funding Available: [qry_Funding totals AND Invoice totals].[SumOf$Funding] - Nz([Sum Of InvoiceAmount],0)

Post the entire SQL statement for analysis.

[dictionary101]

YES!!!!! You ROCK!!!! Thank you very, very, very much!!!

I think I omitted the spaces around the subtraction symbol. Thank you again!

[June7]

Those spaces are not important. Would not make any difference. Access might even throw them in if you forget.

Glad it's working.

[dictionary101]

Hmmm . . . then I don't know what I did when I typed it myself. When I copied the equation from your message and pasted it over what I had it worked! I'm doing the happy dance here.

Thanks again.

[June7]

Might have had some misplaced and/or unpaired parens.