Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can the Date type understand midnight? (2000)

    I have to record the activities of a group of carers and nurses that are often booked to work overnight shifts. At present I am doing this by splitting an overnight shift into two, the bit up to midnight and the bit after midnight, and writing two records to a calendar table. The calendar table is then used to search for carers availability.

    I can't seem to get Access to accept midnight as a valid time. 24:00 is rejected, 12:00 AM becomes 00:00 and 12:00 PM becomes 12:00.
    To make sure that the carers are not double booked I need to test whether a proposed new booking falls within the time already booked. so 00:00 is not going to work. If the booking is from 6 pm to midnight the system will treat an end time of 00:00 as first thing in the morning - before the start time, defeating my cunning tests to see if a time falls within a range.

    My solution is to use 23:59 instead of midnight. It seems to work but I can't help feeling that a special case somewhere will trip my logic up and I'll end up double booking someone. Does anyone know of a better way?

    Ian

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

    Re: Can the Date type understand midnight? (2000)

    Why not use a date field and a time field, or a single field containing date and time? So a shift could be from 2 December 18:00 to 3 December 0:00.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can the Date type understand midnight? (2000)

    But 00:00 *is* midnight. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can the Date type understand midnight? (2000)

    Yes 00:00 is midnight, but of the day before. I want to represent the span of time between (for example) 6 PM and midnight, which is +6 hrs. If I use 00:00 I get -18hrs.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can the Date type understand midnight? (2000)

    I am holding this as my last resort. I have written a number of date/time tracking routines in the application, all of which rely on the times that are being checked or searched for having the same date. It will be a pain to change them all so I was hoping there was another way. Oh well.

    Thanks
    Ian

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

    Re: Can the Date type understand midnight? (2000)

    I fear that you'll have to do some work regardless of the solution you choose:
    - If you use 23:59, you'll have to check for that throughout your code and treat it as a special case.
    - If you use 0:00 and add a special flag field to signal that you actually mean 24:00, you'll have to check throughout your code if this flag is set.
    - If you use date + time, you'll have to adapt all code that relies on the times being on the same day.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can the Date type understand midnight? (2000)

    I would also like to point out that if you don't choose to use a Date&Time stamp, then you can never deal with a shift that spans midnight. That would appear to be a serious design flaw to me.
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can the Date type understand midnight? (2000)

    I think you are probably right. Drat!

Posting Permissions

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