Moving total result from query to report

G

Greg

I created query name Total Amount query. In this query I am calculating
total for each order. The last column in this query is Total_Amount then to
get all totals I put the following code in field row for this column:
Total_Amount:nz([Cash1])+nz([Certified_Check])+nz([Company_Check])+nz([Credit_Card])+nz([On_Pickup])+nz([On_Delivery])-nz([Prepaid])+nz([Origination_Fee])+nz([Gratuity])

The record source for this report is based on different query that I named
Query 1. I would like to place totals to that report however if I set
control source for text box Total Amount on that report to =[Total Amount
Query]!Total_Amount I am receiving an error message. How do I place totals
on that report? Thanks
 
F

fredg

I created query name Total Amount query. In this query I am calculating
total for each order. The last column in this query is Total_Amount then to
get all totals I put the following code in field row for this column:
Total_Amount:nz([Cash1])+nz([Certified_Check])+nz([Company_Check])+nz([Credit_Card])+nz([On_Pickup])+nz([On_Delivery])-nz([Prepaid])+nz([Origination_Fee])+nz([Gratuity])

The record source for this report is based on different query that I named
Query 1. I would like to place totals to that report however if I set
control source for text box Total Amount on that report to =[Total Amount
Query]!Total_Amount I am receiving an error message. How do I place totals
on that report? Thanks
Click to expand...

To read a record in one table or query that is not included in the
record source of a report, use DLookUp().

Assuming that only one record is returned in this query:

=DLookUp("[Total_Amount]","[Total Amount Query]")

Make sure the name of this control is NOT the same as the name of any
field in it's control source expression.

If there were several records returned, you would need to add a Where
clause to the above DLookUp.
See VBA help on DLookUp and Restrict Data to a subset of records.
 
Top