Aggregate Query Based on Dates but Without a Date Field 1

[joatmofn]

My application needs a report to show two things about each service that our company provides (landscape biz, services include mowing, mulching, fertilizer, etc.

The report will show:
The two service items are 1) Count, and 2) Man Hours.

Further, I need to specify several query parameters when running a report. Such as Property and date range.

I have a form that prompts the user for the property and date range. It plugs this information into an aggregate query via VBA. The query uses "group by" and expressions.

If I didn't care about specifying a date range the form, vba, query, and report would be fine.

When I add the date field to the query, it automatically takes on the group by clause which causes the report to show man hours and count per DATE.

I need the report to show the count and man hours for each service within a given data range (not for each date).

I've tried several things but just can't get it to work the way I need it to work.

So, my question is: How can I specify a date range against a query that does not contain a date field. Or perhaps, the question should be: how do I include a date field in my aggragate query without the datefield being a "group by". I tried using min and setting the criteria to an earlier date, but I still get prompted for a date when the report runs.

If you've followed along and understand what I am trying to do and can shed some light on this for me, I will be most grateful.

Thanks.

 

[dhookom]

Change the "Group By" under the date column to "Where".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[joatmofn]

Thanks a bunch. Perfect!! Here's a star for you...

 

[joatmofn]

I spoke to soon. The where selection on an aggregate query unchecks the "show" checkbox. The column goes away upon closing the query define window. If I check the "show" box, a message appears indicating that it cannot be used with an aggregate, or something to that effect.

When using "where", am I to specify something else, perhaps in the criteria field?

Thanks.

 

[dhookom]

According to your first message:
"The report will show:
The two service items are 1) Count, and 2) Man Hours."
I thought you only needed to filter the results by the dates and didn't need to show them. If there is a criteria for the date field, then it will not "go away".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[joatmofn]

You are right in that I need to filter by date and not display the date.

But when where is chosen for the datefield and the query design is saved, the datefield is removed from the query, thus upon running the report, a prompt appears for date as was described in the original post to this thread.

The confusion on my part came when I ran the report after setting the datefield to "where". I misread the report contents and did not notice that the datefield was removed from the query.

Maybe it can't be done.

Thanks just the same.

 

[dhookom]

You can't include the date in a where clause in the OpenReport line with a crosstab like this unless you hide all the details and only display the group and report totals. I hope this is what you need.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.