CODEWITHSUNDEEP
sqldatems-access-2003
Mohammed Rishal
Mohammed Rishal

Reputation: 669

Search between date and time in access query

I have the following data on a table
tbl Admission : Date and Time are two different fields and stored in dd/mm/yyyy format

AdmitDate          AdmitTime 
1/6/2012            00:00
3/6/2012            10:00
3/6/2012            19:00
4/6/2012            20:38
5/6/2012            21:00
7/6/2012            03:00

I would like to write a query to show all records between 3/6/2012 07:00:00 and 5/6/2012 06:59:59.

The query should also have the option of having 'date' input from user (only the date & time can be the fixed interval between 7 am first day to 6 59 am the second day) - for instance using PARAMETERS. I use access 2003. I tried several queries but am not able to perfect one - i do not get accurate results. Somebody please help.

Thanks,

Regards,

Upvotes: 3

Views: 20745

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

If there was only one DATETIME column, the condition for the query would be easy to be constructed:

WHERE AdmitDateTime >= '2012-06-03 07:00' 
  AND AdmitDateTime <  '2012-06-05 07:00'

Since you can't do that and because Access has its own Date and Time functions, I think this will work:

WHERE DateValue(AdmitDate) + AdmitTime 
         >= DateValue([InputDate]) + TimeValue('07:00')
  AND DateValue(AdmitDate) + AdmitTime 
         <  DateValue([InputDate]) + 2 + TimeValue('07:00')

The DateValue() calls are there to ensure that any time parts in the values passed by the user or in the AdmitDate column are chopped off and only the '07:00' and the AdmitTime are used.

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91316

All office dates are numbers, so you can add:

SELECT [admitdate]+[admittime] AS Expr1
FROM dt
WHERE ((([admitdate]+[admittime]) 
  Between #6/3/2012 7:0:0# And #6/5/2012 6:59:59#));

The above will work in the query design window but anywhere else, such as VBA you will run into locale problems.

Edit re comment 

Result
03/06/2012 10:00:00
03/06/2012 19:00:00
04/06/2012 20:38:00

Upvotes: 0

Related Questions