How to calculate Totals?

[Sun_Force]

This is the result of a query that's been used as the record source of a report :

OrderPK	PartName	Treatment	OrderedOn	OrderedBy	Quantity	UnitPrice
12032	Test1	N7	2021/02/22	2	30	1,000
11080	Test2	PL1-17	2021/03/12	2	50	500

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.

 

[arnelgp]

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]

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]

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]

save the sql physically?
on load event of your report:

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)