Between Date and Time Query

[Taff82]

I have a database which records vehicles passing through a weighbridge and records the date and time of weighing.

What I am trying to do is create a query which shows all vehicles that have been weighed between two dates and times.

What I have so far is select blah from blah WHERE (((tblTicketDetails.TDate)>=[Forms]![FrmTransactionRptParameters]![TxtFromDate] And (tblTicketDetails.TDate)<=[Forms]![FrmTransactionRptParameters]![TxtToDate]) AND ((tblTicketDetails.TTime)>=[Forms]![FrmTransactionRptParameters]![TxtFromTime] And (tblTicketDetails.TTime)<=[Forms]![FrmTransactionRptParameters]![TxtToTime]));

However the results returned are not correct.

I would like for example to show all the vehicles that were weighed between:-
From Date 01/01/2004
From Time 10:00
To Date 10/01/2004
To Time 15:00

The results I am getting are all Vehicles that were weighed between 01/10/2004 and 10/01/2004 providing they did not get before 10:00 and after 15:00.

Any help is greatly appreciated.

Thanks

Taff

 

[PHV]

Why 2 fields for a DateTime value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

Hi

It depends how you have defined TDate and TTime in the table. Assuming they are both Date/Time Fields you need to be aware that a date/time field holds the date and time as a number, the part to the left of the decimal point is the date as a number of days, and the part to the right (ie the fractional part) is the time as a number

So just how have you defined the columns in the table?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Taff82]

Hi there,

I have defined TDate as Date/Time - Format Short Date and TTime as Date/Time - Format Short Time.

I use a form to determine the parameters and date parameters are in the Format of dd/mm/yyyy and time is in the Format of hh:nn.

Taff.

 

[PHV]

So, you have two fields storing the same value (as DateTime field are just real numbers) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Taff82]

Maybe?

 

[KenReay]

The point is here, that it would make life much simpler if you had only one column in the table, of type Date/time and storing both the date and the time. You can display the values as seperate controls on forms reports etc if you wish

blah WHERE (((tblTicketDetails.TDate)>=[Forms]![FrmTransactionRptParameters]![TxtFromDate] And (tblTicketDetails.TDateTime)<=[Forms]![FrmTransactionRptParameters]![TxtToDateTime]) );

on the form where you enter the paramter Date/Time, you could have these as two controls but combine then into a single control (with visible set to false) which is used as the paramter in the query (eg txtToDateTime)


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Taff82]

Thanks for the help guys.

Greatly appreciated it, works a treat.

Taff.