Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Use DSum function to retrieve totals
How To
You can use the DSum function to get just about any calculated totals for anything within your database tables. Here is an example:
In this example, a YTD sales amount is needed on the report, regardless of the period being printed.
If this is what you need, you can use the DSum function inside your report footer (or anyplace you choose to place it). To do this, simply add a textbox to the location you want, then add the DSum statement as follows for the recordsource of the textbox:
EXAMPLES:
All records in table
=DSum("[Sales_amt]","tblSales")
Range of dates
=DSum("[Sales_amt]","tblSales","[Sales_date] between #01/01/2003# and #12/31/2003#")
All records for specific customer
=DSum("[Sales_amt]","tblSales", "[Customer_id] = '1234'")
All records for specific customer, with date range
=DSum("[Sales_amt]","tblSales", "[Customer_id] = '1234' and [Sales_date] between #01/01/2003# and #01/31/2003#")
Presumptions:
The field containing the sales amount is called Sales_amt.
The table containing the sales figures is called tblSales.
The field containing the sales date is called Sales_date.
Customer_id is a string datatype, therefore it needs single quote marks wrapped around it. If any field is a numeric datatype, single quotes must not be used, e.g., "[Customer_id] = 1234".
Feedback, comments?
Randy Smith, MCP
rsmith@cta.org![[pc2]](/data/assets/smilies/pc2.gif "[pc2] [pc2]")
In this example, a YTD sales amount is needed on the report, regardless of the period being printed.
If this is what you need, you can use the DSum function inside your report footer (or anyplace you choose to place it). To do this, simply add a textbox to the location you want, then add the DSum statement as follows for the recordsource of the textbox:
EXAMPLES:
All records in table
=DSum("[Sales_amt]","tblSales")
Range of dates
=DSum("[Sales_amt]","tblSales","[Sales_date] between #01/01/2003# and #12/31/2003#")
All records for specific customer
=DSum("[Sales_amt]","tblSales", "[Customer_id] = '1234'")
All records for specific customer, with date range
=DSum("[Sales_amt]","tblSales", "[Customer_id] = '1234' and [Sales_date] between #01/01/2003# and #01/31/2003#")
Presumptions:
The field containing the sales amount is called Sales_amt.
The table containing the sales figures is called tblSales.
The field containing the sales date is called Sales_date.
Customer_id is a string datatype, therefore it needs single quote marks wrapped around it. If any field is a numeric datatype, single quotes must not be used, e.g., "[Customer_id] = 1234".
Feedback, comments?
Randy Smith, MCP
rsmith@cta.org