Retrieve records across dates

[resumes123]

I am using MS Access 2007
I have a table called "price_tab" which has the price data of the stocks that are traded
for the company. It has information of prices round the clock for a day ( all 24 hours )
The fields are id, date, time ,price, volume.
Id is an autonumber that is generated for every record.

I need to the minimum price , maximum price , minimum id, sum of the volume of stocks for each day
within a time period ( for example 9 AM to 5 PM , every day for January month ).

The minimum id will tell me when the trading has started for a day, and max id will tell
when the trading has ended that day.

The query that I created for this is ,

select date, min(id), max(id), min(price), max(price), sum(volume) from price_tab
where date between cdate('01/01/2009') and cdate('01/31/2009')
and time between cdate('9:00:00') and cdate('17:00:00')
group by date.

This works perfectly well.
However, if I would like to get the records of all days in January from 9 PM to 5 AM next day
this query is failing . The query that I wrote is

select date, min(id), max(id), min(price), max(price), sum(volume) from price_tab
where date between cdate('01/01/2009') and cdate('01/31/2009')
and time between cdate('21:00:00') and cdate('5:00:00')
group by date.

The last record in the transaction is till Feb 1, 20009 5 AM
Could some one please suggest a query that I can get the records from 9 PM to 5 AM ( next day )
for all days in January

Can I do the grouping on other fields other than date ?


thanks

 

[PWise]

try
Where Date+time between #1/1/2009# and #1/31/2009 23:59:59#

 

[PWise]

not a good idea to name fields date or time they are reserevd words

 

[resumes123]

pwise,

I tried Date+time between #1/1/2009# and #1/31/2009 23:59:59#, but I am not able to get the result

I am supposed to get records till Feb 1, 2009 5:00:00 AM
But I am not getting the result

 

[PWise]

if this is jus a one time thing change to
Where Date+time between #1/1/2009# and #2/1/2009 5:00:00#