Selective SUM in a text box?

[MrKABC]

Hello!

I would like to add up the currency column in my table for only dollar amounts that are classified as "open", ignoring all records classified as "closed." Table:

STATUS EXPENSE
open 500.00
closed 500.00
closed 500.00

TOTAL EXPENSES: ---> 500.00

I would like to place the total expenses in a text box on a form. (The box is there, it just doesn't work!) Tried SUMIF, and other methods, no luck, I just get a #NAME in the box if I get anything at all.

Thanks in advance!

 

[THoey]

I would think the value stored would have to be created by a query, such as:

SELECT SUM(MyTable.Expense)
FROM MyTable
WHERE MyTable.PrimaryKey = SomeValue AND
MyTable.Status = "OPEN"

The first part of the WHERE clause is just there if you are totalling for a particular account. If not, and you want every OPEN account, remove it...
Terry M. Hoey

 

[rewdee]

Hello MrKABC

I think your problem is that you have a bound form and your text box is not one of the fields. This is why I do not like bound forms. As I see (and someone can show me a better way) there are two options. The first is write an on open event (on current, etc.) that goes something like this:

Private Sub Form_Open(Cancel As Integer)
Dim sSQL As String
Dim rs As Recordset

sSQL = "SELECT Sum(Table1.Amount) AS SumOfAmount FROM Table1 WHERE (((Table1.Status)=" _
& Chr(34) & "open" & Chr(34) & &quot);"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly)
Me.Text4 = rs.Fields(0).Value
rs.Close
Set rs = Nothing
End Sub

Perhaps, though a better and easier method would be to add a field to your current recordsource (query) something like:

SELECT Table1.Status, Table1.Amount, MySum
FROM Table1
WHERE mySum
(SELECT Sum(Amount)
FROM Table1
WHERE Status="Open"

now just have the text box recordsource equal to MySum.

Hope this helps,
Rewdee

 

[tempclerk]

If you're looking for one sum over your whole table, try DSum("[fieldname]","Tablename","Tablename![status field name]='open'&quot