Date & Time Range

[ErinL]

Hi everyone -

This seems like it shouldn't be so difficult but I am just not able to get it right.

I have four text boxes on a form. BeginningDate, BeginningTime, EndingDate and EndingTime. I want to pull all the records that fall between the beginning date and time and the ending date and time (i.e. BeginningDate=5/16/12 and BeginningTime=5:00PM and EndingDate=5/17/12 and EndingTime=3:00AM. I want the query to start on 5/16 at 5:00PM and then list all the records from that point until 5/17 at 3:00AM).

I have tried many variations and none of them have been right. The closest I've come is below:

SELECT qryDockRuns1.Date, qryDockRuns1.RunTime, qryDockRuns1.EmployeeID, qryDockRuns1.Name, qryDockRuns1.Shift, qryDockRuns1.Container
FROM qryDockRuns1
WHERE (((qryDockRuns1.Date) Between [forms]![frmMenuDockRunsNew]![BeginningDate] And [forms]![frmMenuDockRunsNew]![EndingDate]) AND ((qryDockRuns1.RunTime) Between [forms]![frmMenuDockRunsNew]![BeginningTime] And [forms]![frmMenuDockRunsNew]![EndingTime]))
ORDER BY qryDockRuns1.Date, qryDockRuns1.RunTime, qryDockRuns1.Name;

This returns all records for the 15th and 16th both from 3:00AM - 5:00PM

I have also tried using the < and > signs but no records are returned when I do that.

Can someone please help me out with this? I never expected to be having this much trouble with a date and time combination range.

Thank you so much!

 

[apr pillai]

Try your Query with the following SQL:

SELECT qryDockRuns1.Date, qryDockRuns1.RunTime, qryDockRuns1.EmployeeID, qryDockRuns1.Name, qryDockRuns1.Shift, qryDockRuns1.Container
FROM qryDockRuns1
WHERE (((qryDockRuns1.Date)+(qryDockRuns1.RunTime) Between (([forms]![frmMenuDockRunsNew]![BeginningDate])+([forms]![frmMenuDockRunsNew]![BeginningTime])) AND (([forms]![frmMenuDockRunsNew]![EndingDate])+([forms]![frmMenuDockRunsNew]![EndingTime]))))
ORDER BY (qryDockRuns1.Date+qryDockRuns1.RunTime),  qryDockRuns1.Name;

 

[spikepl]

And note that "Date" and "Name" are reserved words, that will get you into trouble sooner or later. http://allenbrowne.com/AppIssueBadWord.html

 

[ErinL]

Sorry for the delay but I am just getting back to this project today.

That code worked exactly like I needed it to!! Thank you so much!