Summing data in QueryDef

[Ken]

I create a QueryDef using the following code:

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM dbo_tblTransactions WHERE
Acct = '" & strAcctNum & "'"
dbs.QueryDefs("qryTransactions").SQL = strSQL

It works fine.

Question: One of the fields includes dollar amounts. How can I add up all
the dollar amounts of the records returnrd in my QueryDef?

 

[John W. Vinson]

Question: One of the fields includes dollar amounts. How can I add up all
the dollar amounts of the records returnrd in my QueryDef?

Where do you want this sum? What's the context - a Form, a Report, VBA code,
in a query...???

John W. Vinson [MVP]

 

[Ken]

John,

I want to sum it and then display it on a form. I know that the results of
QueryDefs are typically displayed in reports, so it'll be interesting to see
whether data can also be used elsewhere. Thanks for your help.

Ken

 

[John W. Vinson]

John,

I want to sum it and then display it on a form. I know that the results of
QueryDefs are typically displayed in reports, so it'll be interesting to see
whether data can also be used elsewhere. Thanks for your help.

You can use a Query anywhere that you can use a Table. Not all queries are
updateable, and Totals queries (to get your sum) never are, so that may impact
your decision.

If you want to display both the individual records *and also* the sum, it's
best to do that directly on the Form rather than trying to do it in the query.
The query would return the records; in the Form Footer you can put a textbox
with a control source such as

=Sum([fieldname])

to sum the values in the form's recordsource.

John W. Vinson [MVP]