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

    Lightbulb Unanswered: Need a little insight on this problem/issue

    Morning all,

    I'm having a little bit of a problem, and require some insight on it.

    What I am trying to do is monitor some travel time for our Service Techs when they are out on a call away from the company.

    The way we calculate it is the following(using the DateDiff() formula).

    We take the time they travel to the plant, and then from the plant back to the hotel.

    Now some of our service guys will not charge travel time from the hotel to the plant if it's a short 15-30 minute drive. So their travel time is from the hotel, back to the airport. But when this happens our travel start time is larger then our plant start time..here's an example of what happens when the two times are larger.

    Good Example:

    Travel Start: 7:00
    Plant In: 8:00
    Plant Out: 17:00
    Travel End: 18:00

    Total Travel time 2 hours.

    Broken Example:

    Travel Start: 12:00
    Plant In: 8:00
    Plant Out: 12:00
    Travel End: 18:00

    Total Travel time 2 hours.(something is wrong with this number )

    As you can see from the last example I've lost 4 hours of travel time from when it started.

    I know how to set it up, but trying to get it to work inside the form based query(don't actually have a query set up for this), it will not work.

    So is there any other way to do this?? This form is a Continuous form as well, so doing it by textbox.value, will change them all..so anyhelp would be great!!

    Thanks guys!!

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I suggest establishing rules for recording the information - if the information is not consistent going in, the results will likely not be consistent going out.

    You can use an "immediate if" in the query to make a decision on how to calculate the values under different conditions, or for more complicated situations, draft a seperate function in a module and reference it in the query.

    tc

  3. #3
    Join Date
    Mar 2006
    Posts
    73
    hmm there's gotta be an eaiser way of doing this..then that solution..I'm still fairly new to the whole programming in Access part, so I'm looking for a simple way of putting it in..

    SELECT DISTINCTROW [Service Hours].ServiceTimeID, [Service Hours].ServiceRecordID, [Service Hours].EmployeeID, [Service Hours].Date, [Service Hours].[Travel Start], [Service Hours].[Plant In], [Service Hours].[Plant Out], [Service Hours].[Travel End], DateDiff("n",[Plant In],[Plant Out])/60 AS Plant, DateDiff("n",[Travel Start],[Plant In])/60+DateDiff("n",[Plant Out],[Travel End])/60 AS Travel
    FROM [Service Hours]
    WHERE (((DateDiff("n",[Travel Start],[Plant In])/60+DateDiff("n",[Plant Out],[Travel End])/60)=IIf([Travel Start]>[Plant In],DateDiff("n",[Travel Start],[Travel End])/60,[Travel])))
    ORDER BY [Service Hours].Date;

    That's what the querry looks like, it's not allowing me to place the iff statement in the or section of the query..

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if your problem entries are only when travel starts after plant in, it should be fixable using the much-despised iif.

    instead of:
    DateDiff("n",[Travel Start],[Plant In])/60+DateDiff("n",[Plant Out],[Travel End])/60 AS Travel

    have a go with:
    iif([Plant In] > [Travel Start], DateDiff("n",[Travel Start],[Plant In])/60+DateDiff("n",[Plant Out],[Travel End])/60 AS Travel, DateDiff("n",[Plant Out],[Travel End])/60 AS Travel)

    or in pseudo-code:
    if arrived after travel started
    ...your calc
    else
    ...start travelling at plant out


    izy

    LATER: small OOOOPS

    have a go with:
    iif([Plant In] > [Travel Start], DateDiff("n",[Travel Start],[Plant In])/60+DateDiff("n",[Plant Out],[Travel End])/60, DateDiff("n",[Plant Out],[Travel End])/60) AS Travel
    Last edited by izyrider; 06-20-06 at 14:26.
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    the much-despised iif
    Yep, "immediate ifs" are slugs when it comes to performance and I agree with izy's sentiments. How about a way around, especially if you want to remain light on code:

    If you only have two situations to worry about, then divide your data into the 2 groups using seperate queries and then use a union query to put the results together.

    Running multiple select queries in this case should not slow the process and will certainly be faster than a single query making a decision at every record.

    tc

  6. #6
    Join Date
    Mar 2006
    Posts
    73
    thank you izy!!!

    it worked out..I knew there was an eaiser way and it wasn't just staring at me right in the face.

Posting Permissions

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