B
bertenbert
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
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