Here is my version, based on Allen's code:
Public Function ESum(Expr As String, Domain As String, Optional Criteria As Variant) As Variant
Dim db As DAO.Database 'This database.
Dim rs As DAO.Recordset 'To retrieve the value to find.
Dim strDomain As String 'Table to look in
Dim varResult As Variant 'Return value for function.
Dim strSql As String 'SQL statement.
'Purpose: Faster and more flexible replacement for DSum()
'Arguments: Same as DSum.
'Return: Value of the Expr if found, else Null.
'Author: Harry Driscoll, based on Allen Browne's ELookup. allen@allenbrowne.com
'Created: May 2014
'Note: Requires a reference to the DAO library.
On Error GoTo errhandler
strDomain = Replace(Domain, "[", "")
strDomain = Replace(strDomain, "]", "")
strDomain = "[" & strDomain & "]"
'Initialize to null.
varResult = Null
'Build the SQL string.
strSql = "SELECT Sum(" & Expr & ") FROM " & strDomain
If Not IsMissing(Criteria) Then
strSql = strSql & " WHERE " & Criteria
End If
strSql = strSql & ";"
'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount > 0 Then varResult = rs(0)
rs.Close
'Assign the return value.
ESum = varResult
Exit_DSum:
Set rs = Nothing
Set db = Nothing
Exit Function
errhandler:
MsgBox Err.Description, vbExclamation, "ESum Error " & Err.Number
Resume Exit_DSum
End Function
