Using SUM and GROUP problems

[jhleblanc]

Here is what I need I am running a report the report shows all the invoices between two dates and total all the subtotals of the invoices and the taxes on the invoices. Here is the code.

strSQL= "SELECT Sum(tblinvoices.fldsubtotal) As fldsubtotal, Sum(tblinvoices.fldpst) As fldpst, tblinvoices.flddate FROM tblinvoices GROUP BY tblinvoices.flddate HAVING tblinvoices.flddate BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"

But it only gives me one of the invoices the GROUP BY makes a mess of it but I need the date as it is part of the record filter. The SUM works but I can't get the right totals without the date.

[jhardman]

You could try this simplified query:

strSQL= "SELECT * FROM tblinvoices WHERE tblinvoices.flddate BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"

and find the sum with asp. Let me know if this helps.

Jared

[jhleblanc]

How would I find the sum with ASP?

[jhleblanc]

Never mind, I got it. I was thinking that if I SUM the date then system can not use the BETWEEN to filter the records but I was wrong so I used SUM on each of the SQL items and IT WORKS!!

[jhardman]

Never mind, I got it. I was thinking that if I SUM the date then system can not use the BETWEEN to filter the records but I was wrong so I used SUM on each of the SQL items and IT WORKS!!

I'm glad you got it to work. Thanks for posting your solution.

Jared