Results 1 to 7 of 7
  1. #1
    Astron2012 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    54

    Question DateTime Difference Query help?

    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

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just check out if below gives some guidelines :

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

  3. #3
    Astron2012 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    54
    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

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    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

    Code:
    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

  5. #5
    Astron2012 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    54
    OK I'll give that a try, Thanks for your help

  6. #6
    Astron2012 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    54
    It works!

    Thank again for your help

  7. #7
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-05-2011, 02:25 PM
  2. Replies: 1
    Last Post: 04-14-2011, 07:19 AM
  3. Replies: 3
    Last Post: 11-19-2010, 01:48 PM
  4. Replies: 0
    Last Post: 06-06-2010, 08:23 AM
  5. Replies: 1
    Last Post: 03-04-2009, 10:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums