How to calculate Totals? (1 Viewer)

Sun_Force

Active member
Local time
Today, 23:33
Joined
Aug 29, 2020
Messages
396
This is the result of a query that's been used as the record source of a report :

OrderPKPartNameTreatmentOrderedOnOrderedByQuantityUnitPrice
12032Test1N72021/02/222301,000
11080Test2PL1-172021/03/12250500

How can I calculate the total and print it in the header?
The result should be :
(30 * 1,000) + (50 * 500) = 30,000 + 25,000 = 55,000

Thanks for any kind of help.

Edit: the query is not a saved query. I built the sql in vba, open the query and set the sql as its record source.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:33
Joined
May 7, 2009
Messages
19,175
create another Query?

select Sum([Quantity] * [UnitPrice]) As [Total] From yourQuery;

on the textbox on Header section of report, set the Control Source to:

=DLookup("Total", "theNewQueryName")

or directly:

=DSum("[Quantity] * [UnitPrice]", "yourQuery")
 

Sun_Force

Active member
Local time
Today, 23:33
Joined
Aug 29, 2020
Messages
396
create another Query?

select Sum([Quantity] * [UnitPrice]) As [Total] From yourQuery;

on the textbox on Header section of report, set the Control Source to:

=DLookup("Total", "theNewQueryName")

or directly:

=DSum("[Quantity] * [UnitPrice]", "yourQuery")
Sorry, I should have explained.

The query is not a saved one.
I build the sql in vba, open the query, and set the sql as its record source.

Any possible way for this situation?

thanks
 

Sun_Force

Active member
Local time
Today, 23:33
Joined
Aug 29, 2020
Messages
396
At present, I'm creating the sql once again in the header's on-format, and calculate the totals with a loop by moving through the result.
I thought there may be some more convenient way to do it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:33
Joined
May 7, 2009
Messages
19,175
save the sql physically?
on load event of your report:



Code:
Dim the_sql_here As String
Const query_name As String = "zzQuery"
Dim qd As DAO.QueryDef
'substitute the name of your table to 'yourTable'
the_sql_here = "select sum([quantity]*[unitprice]) as Total from yourTable;"
On Error Resume Next
Set qd = CurrentDb.QueryDefs(query_name)
If Err Then
    Set qd = CurrentDb.CreateQueryDef(query_name, the_sql_here)
    CurrentDb.QueryDefs.Append qd
End If
Set qd = Nothing
Me!txtboxOnHeader = Dlookup("Total", query_name)
 

Users who are viewing this thread

Top Bottom