Aggregate qry returning record when it shouldn't?

[IvanaLearn]

Using Access 2007: Using VBA wrote a qry that returns the calculated aggregate for a series of records with a common id number. When no records match the "where" criteria, the qry is still returning a record? There is no aggregate of course, but I been unable to determine what IS in that record to trim it off, test for it or otherwise get around the condition. Suggestions?

' Get unique id for all related records
My_co_event_id = co_event_id.Value

Dim db As Database
Dim rst As DAO.Recordset
Dim txtSQLtext As String
Dim Mycount As Integer
Dim Totals As Currency

'SQL statement to retrieve totals from table
txtSQLtext = "SELECT Sum(c.value) AS c_extended "
txtSQLtext = txtSQLtext + "FROM co WHERE ((co.co_event_id) = " + CStr(My_co_event_id) + ")"

'Open connection to current Access database
Set db = CurrentDb()

' Set the record set to open on the query
Set rst = db.OpenRecordset(txtSQLtext, dbOpenDynaset)

If rst.EOF And rst.BOF Then
Totals = CCur(0)
Else
Totals = CCur(rst("c_extended"))
End If


'Set the text box on the form to show the total value
txtTotal.Value = Format(Totals, "Currency")

'Close and remove the recordset
rst.Close
Set rst = Nothing

 

[PHV]

You need only one single line of code:
Me!txtTotal = Format(Nz(DSum("co_value", "co", "co_event_id=" & Me!co_event_id), 0), "Currency")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IvanaLearn]

Thank you SO much: works like a charm and so much more concise. Have to go rewrite some modules. Thanks again!