Capturing the sum of data from a different table

[Moxioron]

Hello all.

I have a report that works great. But I wanted to add a text box that captures the total from another table because it is related to the data in the report.

Table Name: [tbl_Balance Consolidation Report (CD1460)_Updated]
Date Range: Between [#08/01/2012#]And[#10/31/2012#]
Criteria: [Promo Indicator = "P"

I have searched high and low for a solution and can't seem to come up with one.

Any easy way to do this? Thanks.

 

[vbaInet]

You can use the DSum() function:

http://www.techonthenet.com/access/functions/domain/dsum.php

 

[Moxioron]

Thanks for the response. I don't need the date range...but when I follow the instructions on the link, I get 'Error' in the text box. However, if I take out the "Promo Indicator = P", it does sum all of the amount column in that table.

=DSum("Amount","tbl_Balance Consolidation Report (CD1460)_Updated","Promo Indicator = P")

So how do I get the criteria to only return the sum of the Amount field of the promo indicator field = P?

 

[Moxioron]

Solved...

=DSum("Amount","tbl_Balance Consolidation Report (CD1460)_Updated","[Promo Indicator] = 'P'")

 

[vbaInet]

Good job Moxioron. One piece of advice for you, avoid spaces in you table or field names. You've noticed they need to be enclosed in square brackets.