DateTime Difference Query help?

[Astron2012]

Hi,

I have a database containing a schedule for employee's jobs. Each job has a start date/time and end date/time. Some jobs can spread over days or weeks.


I want to create a query where I can bring up all jobs for that day, so this may not mean just by start date as the start date may be for the previous day for example but I still want to see the jobs due to be worked that day.

Could someone help?

Many Thanks

[recyan]

Just check out if below gives some guidelines :

SELECT 
	myTable.Job, 
	myTable.JobStartDate, 
	myTable.JobEndDate
FROM 
	myTable
WHERE 
	(
		((myTable.JobStartDate)<=#4/27/2012#) AND ((myTable.JobEndDate)>=#4/27/2012#)
	);

Thanks

[Astron2012]

Thank you,

I think I could use this. I've thought of a better way for me to try to present the information which would be in a form with a subform. On the parent form header I will have an unbound textbox (txtdate) for the user to select the date they wish to view.

Is there any way then I can use the value in the parent txtbox to bring up the records in the subform for that day where the start date could be the previous day for eg.

Would it be like the following?

SELECT
myTable.Job,
myTable.JobStartDate,
myTable.JobEndDate
FROM
myTable
WHERE
(
((myTable.JobStartDate)<=#[txtdate]#) AND ((myTable.JobEndDate)>=#[txtdate]#)
);

Not sure how I can convert this to the code for the subform on load, or maybe after update in the txtbox?

If you could help I would be really grateful

Thanks

[recyan]

Sorry, not in to Forms & Reports, but what you are saying can definitely be done.
Till, someone comes along :
1) Make a Form say MyForm & have the input text box for the Date. Name it say, TheDate. At a basic level, in the Properties of the input text box, build the event for "On Change" & in the code just add Refresh.
2) Make your query something like

SELECT 
    .......
FROM 
    myTable
WHERE 
    (
        ((myTable.StartDate)<=[Forms]![MyForm]![TheDate]) 
        AND 
         ((myTable.EndDate)>=[Forms]![MyForm]![TheDate]));

where [TheDate] is the name of the text box on your Form.

3) Build a subForm based on your Query & incorporate in the Main Form i.e MyForm.

Then run the Main Form & see what happens.
Mind you - I am hopeless with Forms & Reports. The above is just for you to try something till someone comes along.

Thanks

[Astron2012]

OK I'll give that a try, Thanks for your help

[Astron2012]

It works!

Thank again for your help

[recyan]

Glad you got it working.
Pls test thoroughly, as my skills with Forms & Reports are on a negative scale & there might be some problem in what I have suggested.

Thanks