@LonghronJ
The easiest way to approach this is to edit the SQL of an existing query object via VBA and DAO. So the first thing you need to do is create a Query Object using
Code:
sp_report Actuals show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Jones', SummarizeColumnsBy = 'TotalOnly'
We will call this query, qryCost. With that, you can use code to edit the SQL. The following code uses two local variables that can be replaced with a function or whatever.
Code:
Dim strSQL As String
Dim strName As String
Dim strSummary As String
strName = "Jones"
strSummary = "TotalOnly"
strSQL = "sp_report Actuals show AmountActualCost_Title, " & _
"AmountActualRevenue_Title, " & _
"AmountDifferenceActual_Title, " & _
"Text, " & _
"Label, " & _
"AmountActualCost, " & _
"AmountActualRevenue, " & _
"AmountDifferenceActual " & _
"parameters DateMacro = 'All', " & _
"EntityFilterFullNameWithChildren = '" & strName & "'," & _
"SummarizeColumnsBy = '" & strSummary & "'"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryCost")
qdf.SQL = strSQL
'Do stuff here with your edited query object.
Set qdf = Nothing
Set db = Nothing