Filtering an access report which has an aggregate query as record source

[hiteshgupta]

I have a table with following fields
- SDate
- TestCount

Now, the values in the sdate are typically like 01/01/2012 06:45 PM,01/02/2012 06:45 PM,01/01/2012 07:45 PM,10/10/2012 06:45 PM,11/30/2012 07:45 PM,

Now, I want to create a report which shows data group by the "Time" value under the 'sdate' field and shows the sum of "test count" time wise

So my query is

Select Timevalue(Sdate) , 
sum(TestCount) 
from TestMaster
Group by Timevalue(Sdate)

I am using this query as recordsource in the report.

This works fine..If I call the report from a form , the report is generated correctly..

Now, the problem is while calling the report from the form, I want the report to display data for a specific date range only. So I have two fields called startDate and EndDate on the form.
I am passing these two as parameters while calling the report

docmd.openreport "reportName", acfilterview,,"Datevalue(sdate)>='" & startdate & "' And datevalue(sdate)<='" & endDate & "'"

now, this is not working for me, I figured out the reason as the original query is having aggregate fields only, and passing paramter are non-aggregate, thsi is not working. Can anyone please help me on this?

[MikeTheBike]

Hi

I think you need to delimit and format the date like this

DoCmd.openreport "reportName",,,"Datevalue(sdate)>=#" & format(startdate,mm/dd/y") & "# And datevalue(sdate)<=#" & format(endDate,"mm/dd/yy") & "#"

the dates need formating as a string (US or Universal format) and delimiting with a #.

This is asssuming that startdate and enddate are actualy dates. If they are stings, that are formated correctly and have the # delimiters included, then you do not need the apostrophe delimiter (apostrophes are required for sting fields only).

HTH


MTB

[Rabbit]

You said:

I figured out the reason as the original query is having aggregate fields only, and passing paramter are non-aggregate, thsi is not working.

That is wrong. There is no such thing as a parameter that is an aggregate or non-aggregate. The reason it's not working is the result has no field called SDate.

You need to filter the underlying detail data that is passed to the aggregate query.

[NeoPa]

Rabbit:
That is wrong

While technically correct, I believe the original statement, although not expressed in perfect English, reflects a correct understanding of the issue, if not a full grasp of it exactly. Certainly the fundamental problem.

Let me see if I can explain so all see the point (I'm sure Rabbit will immediately but it may be harder to explain generally.) :

The [SDate] field (which is a TimeStamp rather than Date field) is not included in the aggregated output of the query. It would have to go, logically, at the WHERE point of the aggregate query (IE. Before any aggregation.) and this is not possible from the report call as that can only filter the results of the query, which is essentially equivalent to putting it in at the HAVING point, which is after the aggregation.

Bearing in mind the [SDate] field consists of both Date and Time, I believe the solution is to design a non-aggregated query to support the report, which itself should aggregate the data. To filter the Date data correctly, it must be remembered that the time is included. IE. If a date range is provided as :

([SDate] Between #01/01/2013# And #01/05/2013#)

then the designer should understand that DateTimes on the 5th January 2013 will not be included.