Need a little insight on this problem.

Smythe · June 20th, 2006, 08:08 AM

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.

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 any help would be great!!

Thanks guys!!

mmcdonal · June 20th, 2006, 08:43 AM

Perhaps the reason for your wrong answer in the second example is due to the continuous form as well. It took the value from the first record.

Is there a reason you must have a continuous form?

In any event, where are you taking and storing the travel time value? Is this shown in a text box on the continous form? If so, what does the code look like? The travel start should be checked against the Plant In time, and if it is greater, the Plant Out time perhaps should be used, or the trevel time calculated as the difference between the Travel Start and Travel End times, with no intervening date calculations.

The travel time value should be cacluated as needed, and not permanently stored in the database.

mmcdonal

Smythe · June 20th, 2006, 09:02 AM

Quote:

quote:Originally posted by mmcdonal
Perhaps the reason for your wrong answer in the second example is due to the continuous form as well. It took the value from the first record.

Is there a reason you must have a continuous form?

In any event, where are you taking and storing the travel time value? Is this shown in a text box on the continous form? If so, what does the code look like? The travel start should be checked against the Plant In time, and if it is greater, the Plant Out time perhaps should be used, or the trevel time calculated as the difference between the Travel Start and Travel End times, with no intervening date calculations.

The travel time value should be cacluated as needed, and not permanently stored in the database.

mmcdonal

The reason why it's a continuous form is cause it's a sub form on a seperate form that is tied into the same report.

We store the Tavel Start, Plant In, Plant Out, and Travel End times on a seperate Table..the total amount of travel time is only calculated on the form.

the query that's based right off the form looks like the following:

Code:

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;

I've tried adding in an iff statement in the query to see if that would work, but alas to my trying iot does not.

And yes these are all done in Text boxes.

HTH..

SerranoG · June 21st, 2006, 07:54 AM

In your broken example, if your travel time started at midnight, then travel start should be 0:00, not 12:00. Noon is 12:00. What happens if you change start time to 0:00?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division