how to format a start and end date range in a query and not group.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don - 07 Nov 2005 20:36 GMT

I'd like to create a query using a start and ending date but I don't want the
Query to group-by the date giving me info from every date input into the
system. Instead, I'd like the query to just recognized the data range and
give me a one line resolution of the data requested and the date range as in
a heading, if possible.

Reply to this Message

Denis Dougall - 07 Nov 2005 20:46 GMT

Use the between in the sql.

Denis

> I'd like to create a query using a start and ending date but I don't want the
> Query to group-by the date giving me info from every date input into the
> system. Instead, I'd like the query to just recognized the data range and
> give me a one line resolution of the data requested and the date range as in
> a heading, if possible.

Reply to this Message

Denis Dougall - 07 Nov 2005 20:49 GMT

Here is a sample:
SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

HTH,

Denis

> Use the between in the sql.
>
[quoted text clipped - 6 lines]
> in
> > a heading, if possible.

Reply to this Message

John Spencer - 07 Nov 2005 21:00 GMT

That is very unclear request, but I am going to guess that you want to
something like the SUM of some field for a specified date range and want to
show the requested date range in the query.

In the query grid
Field: ThisField
Total: Sum

Field: YourDateField
Total: Where
Criteria: Between [Enter Start Date] and [Enter End Date]

If you want to display the values you enter in response to the prompts
Field: [Enter Start Date]
Total: Group By

Field: [Enter End Date]
Total: Group By

> I'd like to create a query using a start and ending date but I don't want
> the
[quoted text clipped - 3 lines]
> in
> a heading, if possible.

Reply to this Message

John Vinson - 07 Nov 2005 22:17 GMT

>I'd like to create a query using a start and ending date but I don't want the
>Query to group-by the date giving me info from every date input into the
>system. Instead, I'd like the query to just recognized the data range and
>give me a one line resolution of the data requested and the date range as in
>a heading, if possible.

It won't Group By anything unless you make it a Totals query (by
clicking the Greek Sigma icon).

If you DO want it to be a totals query, use the WHERE operator
(instead of the default Group By) on the date/time field. Use a
criterion such as

BETWEEN [Enter start date:] AND [Enter end date:]

or, if the table field contains time as well as date information, and
if you don't trust your users to always use correctly formatted dates,

>= CDate([Enter start date:]) AND < DateAdd("d", 1, CDate([Enter end date:]))

John W. Vinson[MVP]

Reply to this Message