Adding a txt field to report to display value from another query

[darren_access]

Hi all,

I would like to add a value to the summary line of my report that is part of another query and not the query the report is based on.
Both queries will use the same where clause dependent on a form variable being passed to the report.

Is this possible?

thank you

 

[Pat Hartman]

Yes. If it is just one field you want, you can use a domain function such as DLookup()

 

[darren_access]

Thanks Pat. I'm having trouble with the syntax though. here's the query I want to add the sum of PO_amount from.

SELECT Purchase_Orders_T.project_id, Sum(Purchase_Orders_T.PO_amount) AS SumOfPO_amount
FROM Purchase_Orders_T
WHERE (((Purchase_Orders_T.project_id)=[Forms]![PROJECT LIST].[fld_project_id_key]))
GROUP BY Purchase_Orders_T.project_id;

The query is named qPO_Total

Can you help me with the syntax?

Thanks again

 

[Pat Hartman]

You should be able to use DSum() for this. In the controlSource of the control where you want to display the sum --

=DSum("PO_Amount", "Purchase_Orders_T", "Product_ID = " & Product_ID)

 

[darren_access]

You should be able to use DSum() for this. In the controlSource of the control where you want to display the sum --

=DSum("PO_Amount", "Purchase_Orders_T", "Product_ID = " & Product_ID)

That worked brilliantly. Thanks Pat

 

[Pat Hartman]

You're welcome.