Summary Report

[pe66y1027]

I have a summary report full of unbound text boxes pulling multiple types of
information from a single query (using DCount for the most part). As long as
I leave the query as it is, the report works fine and pulls the correct
information from all of the records in my query. My problem is, I need to
count records from the query based on a date range, and when I add Between
[Start Date] And [End Date] to the criteria line for one of the fields in my
query, every field on my summary report changes to #Error. Any thoughts on
how to fix this?

 

[KARL DEWEY]

Your DCounts need the same criteria.

 

[John Spencer]

Leave the parameter prompts out the query.

Solution A:
Use a form to get the date parameters and reference the form controls in the
query -
[Forms]![MyDateRangeForm]![StartDate] and [Forms]![MyDateRangeForm]![EndDate]

The form must be open so the query can "see" the values in the controls.

Solution B:
Keep the query as is without the parameters and set the date criteria as part
of all the lookups.

Solution C:
Find a way to do all the calculations in the query (or in a set of queries) or
even in the report. This will often end up being the best solution and could
be much faster and more efficient. For instance you could use the report's
report footer section (Hide the detail section) to get a lot of information.

An example, say you have field "FieldX" that records whether something was
done for Business, Pleasure, or Both. You could get a count of the three
responses by adding three controls to the report footer with the following
expressions
=Abs(Sum(FieldX="Business")) to count the number of values that = Business
=Abs(Sum(FieldX="Pleasure")) to count the number of values that = Pleasure
=Abs(Sum(FieldX="Both")) to count the number of values that = Both
And if you wanted to count the number of values that were blank
=Abs(Sum(FieldX & ""="")) to count the number of values that are blank


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[pe66y1027]

Thank you so much John! I used your solutions "A" and "C" and it finally
worked. You have no idea how long I'd been trying to fix this! Thank you
thank you thank you!!

Leave the parameter prompts out the query.

Solution A:
Use a form to get the date parameters and reference the form controls in the
query -
[Forms]![MyDateRangeForm]![StartDate] and [Forms]![MyDateRangeForm]![EndDate]

The form must be open so the query can "see" the values in the controls.

Solution B:
Keep the query as is without the parameters and set the date criteria as part
of all the lookups.

Solution C:
Find a way to do all the calculations in the query (or in a set of queries) or
even in the report. This will often end up being the best solution and could
be much faster and more efficient. For instance you could use the report's
report footer section (Hide the detail section) to get a lot of information.

An example, say you have field "FieldX" that records whether something was
done for Business, Pleasure, or Both. You could get a count of the three
responses by adding three controls to the report footer with the following
expressions
=Abs(Sum(FieldX="Business")) to count the number of values that = Business
=Abs(Sum(FieldX="Pleasure")) to count the number of values that = Pleasure
=Abs(Sum(FieldX="Both")) to count the number of values that = Both
And if you wanted to count the number of values that were blank
=Abs(Sum(FieldX & ""="")) to count the number of values that are blank


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a summary report full of unbound text boxes pulling multiple types of
information from a single query (using DCount for the most part). As long as
I leave the query as it is, the report works fine and pulls the correct
information from all of the records in my query. My problem is, I need to
count records from the query based on a date range, and when I add Between
[Start Date] And [End Date] to the criteria line for one of the fields in my
query, every field on my summary report changes to #Error. Any thoughts on
how to fix this?

.