Access 2000 - Query HELP!: Why does it not sum credits/negative dollar amounts?

[Kevin]

Group By - Client
Sum - PastDue: Iif(DateDiff("d",[InvoiceDate],Date())>30,[InvoiceNetTotal],0)
Sum - Current: Iif(DateDiff("d",[InvoiceDate],Date())<31,[InvoiceNetTotal],0)

These 2 sum fields work fine, EXCEPT it is not summing my negative
numbers/credits. Can not understand why?

[Alan Webb]

Kevin,
Sum - PastDue: is the column title, not a formula. Try something like
TotalPastDue:
Iif(DateDiff("d",[InvoiceDate],Date())>30,Sum([InvoiceNetTotal]),0).
Current balance would be similar. Now, I am fishing here, but if you are
writing SQL to do billing and put a total of the past due invoices in one
column and a total of the current invoices in another column and add them up
I'd probably use subqueries to supply my data to my main query. Something
like:
"SELECT PAST_DUE.TOTAL AS PAST_DUE_BALANCE, CURRENT.TOTAL AS CURRENT_BALACE,
PAST_DUE.TOTAL + CURRENT.TOTAL AS TOTAL_DUE
FROM (SELECT INVOICES.INV_NO, SUM(INVOICES.TOTAL) AS TOTAL WHERE
DATEDIFF("D",INVOICEDATE,DATE())>=30 GROUP BY INVOICES.INV_NO) AS PAST_DUE,
(SELECT SUM(INVOICES.TOTAL) AS TOTAL WHERE
DATEDIFF("D",INVOICEDATE,DATE())<30 GROUP BY INVOICES.INV_NO) AS CURRENT
WHERE PAST_DUE.INV_NO = CURRENT.INV_NO"

"Kevin" <ka***@worldnet.att.net> wrote in message
news:8f**************************@posting.google.c om...

Group By - Client
Sum - PastDue:
Iif(DateDiff("d",[InvoiceDate],Date())>30,[InvoiceNetTotal],0)
Sum - Current:
Iif(DateDiff("d",[InvoiceDate],Date())<31,[InvoiceNetTotal],0)

These 2 sum fields work fine, EXCEPT it is not summing my negative
numbers/credits. Can not understand why?