Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2005
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time spanning 2 days (Access 2003)

    It seems like this would be common and easy to figure out, but I can't get it.

    In our shop, second shift runs from 4:30 pm until 1am the next day. The system is picking up data from the production equipment, including a date/time stamp. Our supervisors need to be able to run a query for "what has been produced this shift" no matter what time it is, before or after midnight. This needs to be done by comparing the system date to the date/time stamp.

    What query criteria can be used to find the correct records?

    Thanks for any ideas.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time spanning 2 days (Access 2003)

    Have you tried

    DateDiff("n",[datestart],[dateend]) formatted as General Time which will give you minutes
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time spanning 2 days (Access 2003)

    You could the following calculated column in a query:

    ShiftedTime: DateAdd("h",-1,[DateTimeStamp])

    where DateTimeStamp is the name of the time stamp field. You can compare this to the date. Times up to 1 AM the following day will be counted as belonging to the current day.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Time spanning 2 days (Access 2003)

    Why dont you introduce a Shift number for the day in the appropraite table and update this at the time of writing away the record.

    In this way all your queries can then just look at this shift number field.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •