sql sum as variable - how to?

[Ange]

hello,

i am trying to get a sum of specified fields as a variable. i tried to do that this way:

Dim db As DAO.Database
Set db = CurrentDb

Dim rst_tmp As DAO.Recordset
Set rst_tmp = db.OpenRecordset("SELECT field FROM table WHERE field=(SELECT SUM(field) FROM table HAVING condition_a='a' AND condition_b='b')")
If Not rst_tmp.EOF Then
rst_tmp.MoveFirst
handle = rst_tmp!field
End If
Set rst_tmp = Nothing

MsgBox (handle)

where handle should contain my sum. But this seem to return and empty recordset. Anybody knows why?

 

[WayneRyan]

Ange,

The DSum function would be easier:

Handle = Nz(DSum("[field]", "table", "condition_a = 'a' AND condition_b = 'b'"), 0)

Wayne

 

[tehNellie]

If you want to do it in SQL and unless I completely misunderstood the original query: (your original query is asking for fields which equal the sum of all fields where the criteria is a or b)

SELECT sum(Field) as Field
FROM table
WHERE condition_a = "a" AND
Condition_b = "b"

would return 4 from the table below.

field  condition_a  condition_b
-------------------------------
1      a            b
2      c            d
1      a            b 
2      a            b

 

[Ange]

ok, one mistake is that i should use having instead of where.

having that table:

field condition_a condition_b
2 a b
3 c b
4 a b



i'd like to receive 6. so i try to append the table with query given earlier, but this doesn't work. any ideas?


EDIT:

Wayne, your method works, but i'd like to know also how to do that with sql

 

[WayneRyan]

Ange,

It looks like tehNellie's last post is your answer.

Wayne

 

[Ange]

right. thank you guys!