Report Date Selection criteria (1 Viewer)

[greaseman]

I've got several reports, some of which report for a selected date range, and others that don't use a date range. All of the reports are fed by their respective queries.

In the queries that need a specific date range, I'm using the "Between [Begin Date] and [End Date], which works fine. Incidentally, these queries use Grouping and Count functions.

I'm trying to change the date-specific queries thusly: Put in a date table that contains the required FromDate and EndDate, and change the logic in the queries to say, "Between [TableDates].[FromDate] and {TableDates].[ToDate], with my intention being to eliminate the need for typing in start and end dates repeatedly and instead use a little date table to do the same thing. However, when I do this, I get an error message similar to this:

"You tried to execute that does not include the specified expression 'Between [TableDates].[FromDate] and {TableDates].[ToDate] as part of an aggregate function.'

Can anyone out there explain what this means, and what I might do to resolve the error? I sure would appreciate all the help I can get.

 

[pbaldy]

Personally, I would have the query refer to form fields rather than a special table. The user will probably be entering the dates into a form anyway, so why not just use them straight from there, avoiding the bloat of an extra table?

 

[greaseman]

True...I agree with you. However, this project makes heavy use of switchboards, and the reports are selected from a switchboard. My thought was to have a form open, where the user would input start / end dates, and when the form closes, write those dates to a table. Then, since there is a switchboard where the reports are selected from, modifying the report queries to use the little date table. However, when I tried this approach, that's when I got the aforementioned error message.

Although I appreciate your reply, you didn't answer my question, which was related to the error I received. Can you help me to resolve the error?

Thanks!

 

[pbaldy]

The message indicates an error in the syntax of the query, which you did not post, so it's hard to tell you exactly where the actual error is. If you post the full SQL, it would increase the odds. In general, the message is usually seen when the basic rule of totals queries is broken: every field in the SELECT portion must either be part of an aggregate function, or be present in the GROUP BY clause.

However, nothing you mentioned would prevent the use of a form for the queries to reference, and I still think it would be simpler in the long run. There are some perils to using a criteria table. Having used that technique myself, I now know better. But it's your app, so do what you want.

 

[strep21]

Greaseman,
Apart from what Paul says .....
is
Between [TableDates].[FromDate] and {TableDates].[ToDate]
an exact copy and past ?
Note the "{" instead of "[" after "and".

 

[Rich]

Why not just put a couple of unbound textboxes on the switchboard?

 

[greaseman]

To Strep21.... No, that was not an exact copy, it was a typo on my part. I didn't hold down the shift key, but thanks for having such good eyes!

To Rich..... I was not aware you could put text boxes onto a switchboard form. Are you saying you can handle a switchboard form much like a regular form? If that's the case, I think you may have given me the answer and help I need!

To all others who responded, Wow! It's great to receive so much advice and help in such a short time! Much better than digging through Microsoft's website (I swear Microsoft hires former government workers just to confuse folks with their jargon and terminology!) and much better also than plowing through books and manuals.

My thanks to all of you!

 

[greaseman]

In particular, thanks to pbaldy for your advice and hints. I think I'm trying to make a mountain out of a mole hill, if you know what I mean.

Thanks again!