Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    73

    Unanswered: DateDiff Question

    Hey all,

    Just a quick question/problem..

    I'm currently using a DateDiff function to calculate some hours..I'm taking in travel time, and work time(Travel Start, Plant In, Plant Out, Travel End)

    Cause I'm currently running into the problem where I seem to be loosing hours of travel if my Travel Start Value is greater then my plant in hours...here's a couple of examples of what I'm talkin about.

    Travel Start: 7:00
    Plant In: 8:00
    Plant Out: 14:00
    Travel End: 15:00
    Total Travel: 2 hours

    Travel Start:12:00
    Plant In: 8:00
    Plant Out: 12:00
    Travel End: 18:30
    Total Travel: 2.5 hours(problem)

    This is where my problem arises...It works fine for when the start is less than the plant in time..but not if it's larger.

    I run this in a query as well..and just so people know this is roughly what the code would look like(just in case people haven't used it)

    DateDiff("n",date1,date2) / 60 *which works well in VBA

    my question is...

    Is there a way to do so using an IIF/IF statement in a form made query?
    Last edited by Smythe1000; 06-16-06 at 12:31.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The first question here, I think, is how are you inputting the hours? In order to use DateDiff the "hours" actually have to be the Date/Times for each of these values. Then 12:00 of one day is not greater than 8:00 of the next day. Obviously your "Start Time" cannot be "greater" than your "Plant In Time"; you can't leave for work after you actually get to work!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2006
    Posts
    73
    the way most of our service employees do it is..if our customer is only a short drive away from their hotel they will not charge them for a 15 minute drive.

    so they start their travel back here after they leave their plant..

    and the inputting of hours is done as 24 hour time...and they are date/time fields in the corresponding Table as well.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Then there should be no problem using DateDiff!

    Travel Start:12:00 - Plant In: 8:00 = 8.0 hrs
    Travel End: 18:30 - Plant Out: 17:00 = 1.30 hrs
    Travel Time = 9.30 hrs
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2006
    Posts
    73
    I loose hours that's my problem..

    sorry the one time should have read 12:00 as in Plant Out(my bad)

    so basically you're starting your travel after you've left...

    so it should be

    Travel Start:12:00 - Travel End:18:30 = 6.5 hrs..

    this is being shown as 2.5 hours instead of 6.5 I've lost 4 hours worth of travel time in the calculations..

    sorry bout that confusion..mis typed the hours hehe

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Try changing

    DateDiff("n",date1,date2 / 60)

    to DateDiff("n",date1,date2) / 60

    Looks like you're converting date2 to hours but not date1!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Mar 2006
    Posts
    73
    Thanks, that works

    now is there a way to have that as an if statement in my query for the fourm??

    I was trying the IIF way, but it's not working the way I had intended...

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Smythe1000
    now is there a way to have that as an if statement in my query for the fourm??

    I was trying the IIF way, but it's not working the way I had intended...
    Hi

    Could you edit your original post to include the correct times and also the desired result? I'm losing track having to jump from post to post....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2006
    Posts
    73
    sure thing..hold on..


    Edit: Updated

Posting Permissions

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