Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Question Unanswered: Update Query Problem with Time Field Showing a Date

    To my great resource group....

    I have a update query that runs to establish and update a Drop Dead ship time, based on a field that shows the last delivery time minus the number of transit hours it takes to get the trailer there. The update expression is:

    IIf(DateAdd("h",-[Release Complete]!Transit,[Release Complete]!DeliveryLast)>#12:01:00 AM# And DateAdd("h",-[Release Complete]!Transit,[Release Complete]!DeliveryLast)<#3:01:00 AM#,DateAdd("h",-([Release Complete]!Transit+3),[Release Complete]!DeliveryLast),DateAdd("h",-[Release Complete]!Transit,[Release Complete]!DeliveryLast))

    This expression also makes sure that the Drop Dead time occurs at least 3 hours after midnight to assure a 3 hour warning to occur on the same day, otherwise move to the previous day before midnight.

    The table it updates has the Drop Dead field formatted to Short Time. Problem is, it seems to be inserting a date on some, but not all fields of 12/29/1899. For the sake of me, I cannot figure out why it is doing this on just some of the records. Anyone run across this before? I have tried some different update approaches but all with the same result. The fields that have that date in them are not calculating right further down the line.

    Any and all help appreciated!!!!!!!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    one of your dates is probably 0. The default date in access is 1/1/1900.

    Example:
    dateadd("h", -3, 0) = 12/29/1899 9:00:00 PM

    That said, my guess is for certain rows one of the fields in [Release Complete] is zero.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    Hmmm.....Microsoft, gotta love em'! Thanks for the tip, I'll double check my table and see if that's the case.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Bob.Carter.17
    Hmmm.....Microsoft, gotta love em'! Thanks for the tip, I'll double check my table and see if that's the case.
    I think there are lots of things you can blame Microsoft for...
    ..but I don't think you can blame Microsoft for that one.. In the systems world you've got to start you dates from somewhere.. UNIX systems (and their 'think' alike start from 1970 usually).
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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