Between Date and Time Query

[Taff]

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

 

[mcadle]

Confusing as that is, try this SQL statement:

SELECT * FROM table_name WHERE tblTicketDetails.TDate BETWEEN (([Forms]![FrmTransactionRptParameters]![TxtFromDate]) AND ([Forms]![FrmTransactionRptParameters]![TxtFromTime])) AND (([Forms]![FrmTransactionRptParameters]![TxtToDate]) AND ([Forms]![FrmTransactionRptParameters]![TxtToTime]));

You might need to give or take a few () but you get the idea. Hope this works.

 

[Jon K]

Since DateTime fields are internally stored as double precision numbers, you can combine a date with a time using the + operator e.g. [TDate]+[TTime]

If the text boxes on the form are not formatted as Short Date and Short Time, you may need to declare the data type of the parameters as well in the query like this:-

PARAMETERS [Forms]![FrmTransactionRptParameters]![TxtFromDate] DateTime,
[Forms]![FrmTransactionRptParameters]![TxtFromTime] DateTime,
[Forms]![FrmTransactionRptParameters]![TxtToDate] DateTime,
[Forms]![FrmTransactionRptParameters]![TxtToTime] DateTime;
SELECT *
FROM tblTicketDetails
WHERE [TDate]+[TTime] Between [Forms]![FrmTransactionRptParameters]![TxtFromDate] + [Forms]![FrmTransactionRptParameters]![TxtFromTime] And
[Forms]![FrmTransactionRptParameters]![TxtToDate] + [Forms]![FrmTransactionRptParameters]![TxtToTime];

.