PDA

View Full Version : Sum amounts of field on multiple records



bazzanoid
07-17-2009, 08:00 AM
Afternoon all,

My VB doesn't want to work :(

In plain English, I'm trying to get it to check the table Orders and sum the Amount field where the Status field equals a specific text string(i.e. get a total figure on a set of records). This is what I have, and it doesn't seem to work.....

Dim TotOustanding As Currency

Set rs = Vars.cn.Execute("SELECT * FROM Orders WHERE Status = 'Monthly Account - Posted' ORDER BY DatePaid ASC")

TotOutstanding = DSum("[TotalAmount]", "Orders", "[Status] = 'Monthly Account - Posted'")

Sheets("Summary").Range("K24").FormulaR1C1 = CStr(TotOutstanding)(

Any thoughts as to what I'm doing wrong?

Thanks!

Barry

Bob Phillips
07-17-2009, 08:19 AM
Why not sum it in the SQL



Set rs = Vars.cn.Execute("SELECT SUM(TotalAmount) FROM Orders WHERE Status = 'Monthly Account - Posted' ORDER BY DatePaid ASC")