OK, now we have the query SQL. If we create a named query from that SQL, we can use that named query in a dSum function.
Here's the VBA UDF to create a named query from a SQL string:
sQueryName is the name you want to give to the named query, and sSQL is the query string to use.
Code:
Public Function fcnCustomizeSQL(sQryName As String, sSQL As String) As Boolean
On Error GoTo fcnCustomizeSQL_Error
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
fcnCustomizeSQL = True
Set db = CurrentDb
On Error Resume Next
With db
.QueryDefs.Delete (sQryName) 'Delete the query if it exists
On Error GoTo fcnCustomizeSQL_Error
Set qdf = .CreateQueryDef(sQryName, sSQL) 'Create the query
End With
db.QueryDefs.Refresh
fcnCustomizeSQL_EXIT:
Set qdf = Nothing
Set db = Nothing
Exit Function
fcnCustomizeSQL_Error:
Select Case Err
Case 3075
fcnCustomizeSQL = False
Case Else
Msgbox Err.Number & ", " & Err.Description, vbOKonly, " fcnCustomizeSQL"
fcnCustomizeSQL = False
End Select
Resume fcnCustomizeSQL_EXIT
End Function
So in your form load event,
Code:
Dim strSQL as string
strSQL = "SELECT Invoices.[Invoice ID], Customers.Company, Invoices.[Invoice ID], [Invoices]![Original Total]-(Nz([CustomerPaymentsSum]![SumOfCustomer Payments])+Nz([Customer Deposits]![Customer Deposit],0)) AS Bal, Orders.[Order ID], Invoices.[Original Total], Orders.[Status ID]FROM (((Customers RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]) INNER JOIN Invoices ON Orders.[Order ID] = Invoices.[Order ID]) LEFT JOIN CustomerPaymentsSum ON Invoices.[Invoice ID] = CustomerPaymentsSum.[Invoice ID]) LEFT JOIN [Customer Deposits] ON Orders.[Order ID] = [Customer Deposits].[Order ID] ORDER BY Customers.Company;"
Call fcnCustomizeSQL("MyQuery",strSQL)
Now you have a named query in the Navigation pane. You can use it in your dSum:
Code:
txtBox16 = dSum("Bal","MyQuery")