# Thread: Need a little insight on this problem/issue

1. Registered User
Join Date
Mar 2006
Posts
73

## 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. Registered User
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. Registered User
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. Cavalier King Charles
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.

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
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.

5. Registered User
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. Registered User
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
•