Trying to sum a query

[lawdy]

I want to sum the results of the query listed below.

Public Function modQue_dayTotal2()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim strSvc As String
Dim dateDate As Date
Dim dblAmt As Double

strSvc = "wash"
pubDateDue = DateValue("9/3/2025")
Set db = CurrentDb
sql = "SELECT * FROM tblCalendar WHERE fDate = #" & pubDateDue & "# " _
& "AND fTxtSvc = '" & strSvc & "'"
Set rs = db.OpenRecordset(sql)

Do While Not rs.EOF
dblAmt = rs!fDblRevenue
rs.MoveNext
dblAmt = dblAmt + dblAmt
Loop
Debug.Print "dblAmt " & dblAmt


rs.Close
Set rs = Nothing
Set db = Nothing

End Function


This works, but it looks like DSum would be a much better way of doing it. I have not been able to figure out the code for that. Any ideas?

[xps35]

Try

DSum("fDblRevenue", "fDblRevenue", "fTxtSvc = 'wash' AND fDate = #09-03-2025#")

[lawdy]

Thanks xps35. That works and I don't need the query at all. Thanks again.

[Welshgasman]

https://support.microsoft.com/en-gb/...3-529a322be41b

[NoellaG]

if you want to total a field the agonizing RBAR solution of looping through records is the slowest and most resource consuming solution.
Better is the DSum option
The fastest solution is the totals query option:

select sum(fDblRevenue) from tblCalendar WHERE fDate = #" & pubDateDue & "# " _
& "AND fTxtSvc = """" & strSvc & """"

with an index on the criteria fields. Of course, check the update/index usage ratio before creating the index.