Recordset Sum

[DMT Dave]

Can I add a sum in the line some where ? ie: Sum CostOfJob and Sum VAT and Sum Total ??

Set rs = CurrentDb.OpenRecordset("Select tblInvoices.InvoiceDate, tblInvoices.Name, tblInvoices.InvoiceNumber, tblInvoices.CostOfJob, tblInvoices.VAT, tblInvoices.Total, tblInvoices.Paid, tblInvoices.PaidDate From tblInvoices WHERE InvoiceDate Between #" & mStartDate & "# And #" & mEndDate & "# ORDER BY InvoiceDate;")

[June7]

Only if you make it an aggregate (GROUP BY) query. Total is not a field so how can you sum it?

[DMT Dave]

Hi June7 thank you for your reply, total is a field i am looking for the following

CostOfJob (Sum)
VAT (Sum)
Total (Sum)

Then if there is x 4 names (customers) within that period, this means there is x 4 Invoices for that customer, i am looking to our put to excel the following

From
Joe Bloggs CostOfJob £20.00 VAT £5.00 Total £25.00
Joe Bloggs CostOfJob £20.00 VAT £5.00 Total £25.00
Joe Bloggs CostOfJob £20.00 VAT £5.00 Total £25.00
Joe Bloggs CostOfJob £20.00 VAT £5.00 Total £25.00

To:

Joe Bloggs CostOfJob £80.00 VAT £20.00 Total £100.00

Hope this makes sense ?

[June7]

Ooops, I missed the Total field in the SQL.

Advise still applies. Use an aggregate (GROUP BY) query or build report. Report allows display of detail data as well as summary calcs.

[DMT Dave]

Hi June7, yes it make sense to add criteria's and sums to query and pick up from that

Thank you very much