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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
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.
Click to expand...

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:]))
Click to expand...

John W. Vinson[MVP]
 
Back
Top