Totalling records in a given date range

[Jon M.]

I have a query grouped by date with various fields populated by numbers. I
enter a date range for example: between 1/1/2008 and 1/5/2008, my query is
set to display the totals for each individual day, by both specific field and
also has a grand total column totalling all of my fields. I would like my
query to run so that it does not display each date individually, instead it
would only display the totals of my selected date range in the smae results
it does now for each individual day. if that makes sense. Basically I want
to use a date range in my criteria and display the total of each field and
the grand total of all my fields in one grouping not by individual date which
is what it is currently doing. As you can see I'm lost. Thanks for any
advice you can offer.

 

[John W. Vinson]

I have a query grouped by date with various fields populated by numbers. I
enter a date range for example: between 1/1/2008 and 1/5/2008, my query is
set to display the totals for each individual day, by both specific field and
also has a grand total column totalling all of my fields. I would like my
query to run so that it does not display each date individually, instead it
would only display the totals of my selected date range in the smae results
it does now for each individual day. if that makes sense. Basically I want
to use a date range in my criteria and display the total of each field and
the grand total of all my fields in one grouping not by individual date which
is what it is currently doing. As you can see I'm lost. Thanks for any
advice you can offer.

I presume you have a Totals query; open it in query design view and change the
Totals row for the date field from "Group By" to "Where" (and, if Access
doesn't do so automatically, uncheck the Show checkbox in that column).

This will let you use the date field as a criterion without displaying or
grouping by the date value.

John W. Vinson [MVP]

 

[Jon M.]

--
Jon M.

I presume you have a Totals query; open it in query design view and change the
Totals row for the date field from "Group By" to "Where" (and, if Access
doesn't do so automatically, uncheck the Show checkbox in that column).

This will let you use the date field as a criterion without displaying or
grouping by the date value.

John W. Vinson [MVP]

**That worked great! I have one more question. is there a way I can either
in the query or in a report automatically display the date range I've chosen?
So it has a field or a text box that says what date range you're looking at?

 

[John W. Vinson]

**That worked great! I have one more question. is there a way I can either
in the query or in a report automatically display the date range I've chosen?
So it has a field or a text box that says what date range you're looking at?

I would suggest using a Form to solicit the date range. If you have an unbound
form frmCrit with textboxes txtFrom and txtTo, you can base your report on a
query with criteria

= [Forms]![frmCrit]![txtFrom] AND < DateAdd("d", 1, [Forms]![frmCrit]![txtTo])

You can then set the control source of a textbox on the form to

= [Forms]![frmCrit]![txtFrom]

and similarly for txtTo.

Put a command button on frmCrit to launch the report.

John W. Vinson [MVP]