Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Reports FAQ

How To

Use DSum function to retrieve totals by randysmid
Posted: 24 Jan 03 (Edited 23 Oct 03)

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:

All records in table

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#")

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

Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close