You could have the parameters refer to controls on a form (hidden controls,
if desired) and fill in the value of those controls in code. This would set
the parameters but allow you to open another copy of the query and still
have the parameters' values available. You could then use a DSum() function
with the query as a source.
You could also use a totals query as you mentioned, but do it all in code.
Don't create the Query object. You can do this by leaving the name of the
query as an empty string when you create a QueryDef object variable. Once
you've evaluated the parameters, you could use those values to make your SQL
statement.
Another possibility is to change the SQL of the current query in code then
change it back again (qdf.SQL = "SELECT ..."). If you do this, you could
evaluate the parameters first, but assign those values to variables then
hard code them into the SQL by concatenating the values in instead of
leaving them as parameters.
And, as you pointed out, you could total it yourself by stepping through the
recordset. However, if the recordset gets very large you will find this to
be very slow compared to letting Access handle this with its built-in
abilities.
--
Wayne Morgan
MS Access MVP
"BerkshireGuy" <bd*****@yahoo.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
I have the following code:
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined,
intNumOfWasted, intNumOfApproved As String
Dim QDF As QueryDef
Dim PARAM As Parameter
Set DB = CurrentDb()
Set QDF = DB.QueryDefs("qryHealthReceivedAutomated")
For Each PARAM In QDF.Parameters 'Loop through & eval
Parameters
PARAM.Value = Eval(PARAM.name)
Next PARAM
Set RS = QDF.OpenRecordset(dbOpenDynaset)
RS.MoveFirst
RS.Close
Set RS = Nothing
DB.Close
Set DB = Nothing
-------------------------------------------------------------------------------------------
QryHealthReceivedRevised is not a totals query.
Is there a way to return the totals of QryHealthReceivedRevised
without making it a Totals query? (I would just make a copy of
QryHealthReceivedRevised and make it a totals query, but they want as
few objects as possible)
I was thinking something like : Set QDF = DB.QueryDefs("SELECT
COUNT(PolicyNumber), Sum(Premium) FROM qryHealthReceivedAutomated") but
that doesnt work because I need to evaluate the parameters first.
Should I just step through the recordset and total it that way? Or is
there a better way to do this?
