[RESOLVED] Trying to get SUM total

[aikidokid]

I have a table with 6 fields and I am trying to get the total of one of them, Finance_Expenditure.
I have the following and I am getting '0' returned everytime.

    Dim TotalEx As Currency
    strSQL = "SELECT SUM (Finance_Expenditure) as TotalEx FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    dblExpenditure = TotalEx

Do I have to something with grouping, although I cannot see why as this is the only field I am working with!

[szlamany]

Your local VB side variable TotalEx has nothing to do with a column name in a SELECT statement.

The "rs" object has your value - you can use rs(0) to get it - or use rs("TotalEx") to get at it.

By the name of the dblExpenditure variable you are scaring me that it might be a double-float variable. That should never, ever, ever be used with monetary values - they should be put into a CURRENCY datatype or a DECIMAL(x,y) datatype.

You might want to do it like this:

Dim TotalEx As Currency
    strSQL = "SELECT SUM (Finance_Expenditure) as TotalEx FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    TotalEx = CCur(rs(0))

That would use your already declared TotalEx variable.

[salvelinus]

What data type is Finance_Expenditure?
Anyway, you never use the recordset to set dblExpenditure.

dblExpenditure = rs.Fields("TotalEx")
MsgBox dblExpenditure

It's bad practice (imho) to use the same variable name as a field name, because you often may forget which is which. Your code sets dblExpenditure to the value in the currency variable TotalEx, but you're thinking it's the recordset field TotalEx. Two different objects that you've given the same name.
For example:

Dim TotalEx As Currency
TotalEx = 500
    strSQL = "SELECT SUM (Finance_Expenditure) as TotalEx FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    dblExpenditure = TotalEx
MsgBox dblExpenditure

will show 500.
Another possible caveat: dblExpenditure is presumably a double, but TotalEx is declared as Currency. Never used Currency data type, may not be compatible with double. Don't know about that.

[aikidokid]

By the name of the dblExpenditure variable you are scaring me that it might be a double-float variable. That should never, ever, ever be used with monetary values

Sorry to say this is so, but I will go through and change it. I didn't know this.

What data type is Finance_Expenditure?

It is currency.
TotalEx is just a variable I added for this function, it is not a field name.

So, provided I get the doubles changed to currency variables, this should work:

Dim TotalEx As Currency
    strSQL = "SELECT SUM (Finance_Expenditure) as TotalEx FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    TotalEx = CCur(rs(0))

Could somebody explain about using rs(0)
What is the 0 for/doing?

Ans also, if I didn't declare 'TotalEx ' what would / could I use instead?

[szlamany]

This

Dim TotalEx As Currency
    strSQL = "SELECT SUM (Finance_Expenditure) as TotalEx FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    TotalEx = CCur(rs(0))

Could just as easily been

Dim SomeVariable As Currency
    strSQL = "SELECT SUM (Finance_Expenditure) as CallMeAnything FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    SomeVariable = CCur(rs(0))

TotalEX or SomeVariable in your VB code is just that - a variable.

rs(0) means to take the first column of the recordset - and since you recordset only has one column - rs(0) works fine in my book.

Some people say never use ordinal positions (that's what the 0, 1, 2... are called) - but instead say to always use a named reference.

That would be this instead...

Dim SomeVariable As Currency
    strSQL = "SELECT SUM (Finance_Expenditure) as CallMeAnything FROM tbl_Finances"
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    SomeVariable = CCur(rs("CallMeAnything"))

[szlamany]

... dblExpenditure is presumably a double, but TotalEx is declared as Currency. Never used Currency data type, may not be compatible with double. Don't know about that.

Actually it's the DOUBLE that's evil. DOUBLE math will lose pennies - exactly what you don't want to happen in a financial application!!!

20/5 in double can be 3.99999999999999 - it's just the way floating point math works. It might still display as 4 - but internally it's 3.999999999999999 and that lost digit will eventually burn you.

[salvelinus]

Well, either way, the main problem is not using the rs value.
I really hate to disagree with szlamany, who knows a heck of a lot more than me, but I prefer to use rs.Fields("someField") just to make things clear to anyone coming after me, including myself a year down the road.

[szlamany]

I really hate to disagree with szlamany, who knows a heck of a lot more than me, but I prefer to use rs.Fields("someField") just to make things clear to anyone coming after me, including myself a year down the road.

We nearly always use the ORDINAL because in most of our apps we loop through the columns and use the .NAME from the recordset to determine what to do with the column. Barely ever have a variable set like the OP does.

Otherwise I would have to agree that using the "somefield" is better form.

[aikidokid]

Thanks guys, that's a really good in depth explanation for me, and answers a few more questions I had coming up.
I'm going to rate you both