What's the opposite of DSum()?

A member recently posted a request in the forums for a function that does the opposite of DSum(). The intent was to have a query with a running total but using subtraction instead of addition. I never heard of anyone needing to do something like this before, so it was no surprise that I couldn't find a built-in function that does the opposite of DSum() or Sum() to offer the member.

I really don't know if anyone would ever need to do this again, but I just thought to share what I came up with in case it may be useful to someone else. The following function is very basic and does not include any error handling. Hopefully, you can modify it to suit your needs. I would happily accept any comments or recommendations to improve this function to better help those who may want to use it.

Public Function DDiff(strField As String, strDomain As String, _
    Optional strCriteria As String) As Variant

'accessmvp.com/thedbguy
'7/26/2013

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim varDiff As Variant

strSQL = "SELECT [" & strField & "] FROM [" & strDomain & "] "

If strCriteria > "" Then
    strSQL = strSQL & " WHERE " & strCriteria
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
    If Not (.BOF And .EOF) Then
        varDiff = .Fields(strField)
        .MoveNext
    End If
    Do While Not .EOF
        varDiff = varDiff - .Fields(strField)
        .MoveNext
    Loop
   .Close
End With

DDiff = varDiff

Set rst = Nothing
Set dbs = Nothing

End Function

To see the discussion that started it all, including a pure SQL solution, click here.

Original post date: July 27, 2013