Question time elapsed between 20:00 and 6:00 paid with extra wage

bertenbert

New member
Joined
Aug 1, 2008
Messages
2
Programming Experience
Beginner
Hello guys,

First of all: "it's great to have a site where one can post all his/her problems regarding access.

I'm working on a database where all "trips" of different cab drivers working in the company are introduced stating their place and time of departure and arrival.

The time of departure and arrival is then used in a query where the time passed by the drivers gets allocated to a type of trip. Every type of trip is paid differently.

For example: a trip longer then 4 hours is paid 50% more than a normal trip. A trip over night is paid double.

To calculate the time elapsed on a trip over midnight (and this without introducing a date of departure and a date of arrival), one should use a special expression in his query.

Thanks to some nice friends, I use the next expression:
IIf( [Arrival] < [Departure], DateDiff("n", [Departure], [Arrival] + 1), DateDiff("n", [Departure], [Arrival]) )

When departure is at 23:00 and arrival at 1:00, the result is 120 minutes (just perfect).

One more problem however to tackle: as said trips are separated in different types and each type is paid differently.

Well now: every minute spent on the road between 20:00 and 6:00 the next morning is rewarded (next to the normal wage) with an extra allowance.

I have already tried writing a function (in a module) called "night" ("nacht" in Dutch) in VBA, but it does not work as it should. I use if-statements:

if arrival < #20:00:00# and departure < #6:00:00# then
...
elseif ...

How do you write in VBA: when departure time (for example 17:00) comes before 20:00:00?

Should time in VBA also be written between #?

In this function I also use the formula "IIf( [Arrival] < [Departure], DateDiff("n", [Departure], [Arrival] + 1), DateDiff("n", [Departure], [Arrival]) )" in order to calculate the time passed over midnight.

I hope I'm not being to much of a pain in the neck (with this big exposé), but I hope some of you have an idea and could help me out writing this function.

I'll give some examples (there quite a lot of them):

1)
Departure
20:00

Arrival
6:00

Extra allowance
10 HOURS (20:00-6:00)

2)
Departure
20:00

Arrival
5:00

Extra allowance
9 HOURS (20:00-5:00)

3)
Departure
20:00

Arrival
7:00

Extra allowance
10 HOURS (20:00-6:00)

4)
Departure
21:00

Arrival
6:00

Extra allowance
9 HOURS (21:00-6:00)

5)
Departure
19:00

Arrival
6:00

Extra allowance
10 HOURS (20:00-6:00)

6)
Departure
4:00

Arrival
8:00

Extra allowance
2 HOURS (4:00-6:00)

7)
Departure
21:00

Arrival
5:00

Extra allowance
8 HOURS (21:00-5:00)

8)
Departure
17:00

Arrival
23:00

Extra allowance
3 HOURS (20:00-23:00)


I appreciate your help!

Thanks,

Bert
 
Back
Top