summing amount in a report based on date ranges

[gordytech]

i have a sales table that includes the following fields; date, amount, custname

the report has grouping for cust name

i would like the report to show the the cust name and the amount purchased each month

in the group footer i was intending to have 12 controls with each one doing sum, iif for that month

question is - where do i list the field i want the sum to act on in the following equation

=Sum(IIf([DATE] Between (12/31/2014) And (1/31/2015),0))

 

[alansidman]

Instead of using your table as a record source, use a query as the record source. Create your criteria in your query and then have the report based on the query.

 

[gordytech]

thanks, but the same question for me applies to making it part of a query, if I create a field in the query and want it to sum the month of january;

how do i do that?

JAN: Sum(IIf([DATE] Between (12/31/2014) And (1/31/2015),([AMOUNT]),0)) doesnt work

or do i put the date range in the criteria line

 

[alansidman]

A two step process. Create a query that limits your activity to the date range. Put the date range in the criteria using the Between function.

Then create a second query using the first and group your query on the fields you need to be totalled. See the following link

MS Access: Sum Function