Query doesn't find record with on start date 1

[BrianLe]

In AC97, I have the following query to return records that have a start date between a forms start and finish date.

SELECT tblCarsUnloaded.TrainID, tblCarsUnloaded.TrainPartID, tblCarsUnloaded.CarsUnloaded, tblCarsUnloaded.Weight, tblCarsUnloaded.StartUnload, tblCarsUnloaded.FinishUnload, tblCarsUnloaded.CrewSize, tblCarsUnloaded.HandlingOptionNo, tblCarsUnloaded.Notes, tblCarsUnloaded.TStamp, tblTrain.TrainYear, tblTrain.TrainNo, tblHandlingOption.HandlingOptionNo, tblHandlingOption.HandlingOption
FROM (tblCarsUnloaded INNER JOIN tblTrain ON tblCarsUnloaded.TrainID = tblTrain.TrainID) INNER JOIN tblHandlingOption ON tblCarsUnloaded.HandlingOptionNo = tblHandlingOption.HandlingOptionNo
WHERE ((([tblCarsUnloaded].[StartUnload])>=[Forms]![frmCarsUnloadedReport]![tbxStartUnload] And ([tblCarsUnloaded].[StartUnload])<=[Forms]![frmCarsUnloadedReport]![tbxFinishUnload]));

Generally it works. However, it doesn't find the record when the start unload date (=[Forms]![frmCarsUnloadedReport]![tbxStartUnload]) and the finish unload date (=[Forms]![frmCarsUnloadedReport]![tbxFinishUnload]) are the same as the date the cars were unloaded
(tblCarsUnloaded.StartUnload).

The start date in tblCarsUnloaded.StartUnload is actually a general date with date/time (mm/dd/yy hh:nn AM/PM), and the date in Forms]![frmCarsUnloadedReport]![tbxStartUnload]is only the date (mm/dd/yy) selected from a calendar control.

I've found that if the finish date [frmCarsUnloadedReport]![tbxFinishUnload]is changed to one day more that the start date, then the record is returned.

Any suggestions on how to also be able to return the record for the train unloaded, when the selected start and end dates are the same as the train unload date?

Thanks,

Brian

 

[KenReay]

Hi

As you recognise, "Date" fields are actually date/time fields. Have a look at the format property of the column tblCarsUnloaded.StartUnload and set it to shortdate. If this is not acceptable for other reasons within your application, try

...WHERE (((Format([tblCarsUnloaded].[StartUnload],'yyyymmdd'))>=[Forms]![frmCarsUnloadedReport]![tbxStartUnload] And (Format([tblCarsUnloaded].[StartUnload],'yyyymmdd'))<=[Forms]![frmCarsUnloadedReport]![tbxFinishUnload]));


Regards

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

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

UK

 

[BrianLe]

Thanks. Used your suggested WHERE statement. Just had to modify the "yyyymmdd" format to mm/dd/yy. Now I have

WHERE (((Format([tblCarsUnloaded].[StartUnload],'mm/ddyy'))>=[Forms]![frmCarsUnloadedReport]![tbxStartUnload] And (Format([tblCarsUnloaded].[StartUnload],'mm/dd/yy'))<=[Forms]![frmCarsUnloadedReport]![tbxFinishUnload]));

Thanks again,

Brian