Adding a txt field to report to display value from another query (1 Viewer)

darren_access · Feb 19, 2018

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 · Feb 19, 2018

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

darren_access · Feb 19, 2018

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 · Feb 19, 2018

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 · Feb 19, 2018

Pat Hartman said:
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 · Feb 19, 2018

You're welcome.

Adding a txt field to report to display value from another query (1 Viewer)

darren_access

Registered User.

Pat Hartman

Super Moderator

darren_access

Registered User.

Pat Hartman

Super Moderator

darren_access

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread