A question - please HELP

[nadav.katz]

Hello,

I am facing the following problem....I am an access newbie:

I have to make a form which has the following controls - DayHours,
NightHours, TotalHours.

Day shifts are defined as shifts from 7am until 6pm
Night shifts are defined as shifts from 6pm until 7am

I have a table with EmployeeID, StartTime, EndTime.

As an example, if exmployee X worked from 1pm until 10pm - that should
count as 6 day work hours and 3 night work hours.

How could I implemet such a system that works out the day and night
work hours based on the shift times???
Many thanks in advance,

Nadav

[Roger]

On Jun 11, 4:13*am, [e-mail] wrote:

Hello,

I am facing the following problem....I am an access newbie:

I have to make a form which has the following controls - DayHours,
NightHours, TotalHours.

Day shifts are defined as shifts from 7am until 6pm
Night shifts are defined as shifts from 6pm until 7am

I have a table with EmployeeID, StartTime, EndTime.

As an example, if exmployee X worked from 1pm until 10pm - that should
count as 6 day work hours and 3 night work hours.

How could I implemet such a system that works out the day and night
work hours based on the shift times???

Many thanks in advance,

Nadav

what is the datatype of the startTime and endTime fields ? datetime ?
long integer?

[nadav.katz]

On 11 יו×*×™, 15:57, Roger <[e-mail]:

On Jun 11, 4:13Â*am, [e-mail] wrote:

Hello,

I am facing the following problem....I am an access newbie:

I have to make a form which has the following controls - DayHours,
NightHours, TotalHours.

Day shifts are defined as shifts from 7am until 6pm
Night shifts are defined as shifts from 6pm until 7am

I have a table with EmployeeID, StartTime, EndTime.

As an example, if exmployee X worked from 1pm until 10pm - that should
count as 6 day work hours and 3 night work hours.

How could I implemet such a system that works out the day and night
work hours based on the shift times???

Many thanks in advance,

Nadav

what is the datatype of the startTime and endTime fields ? datetime ?
long integer?-הסתר טקסט מצוטט-

-הר�ה טקסט מצוטט-

Datetime....

[Roger]

On Jun 11, 7:06Â*am, [e-mail] wrote:

On 11 יו×*×™, 15:57, Roger <[e-mail]:

On Jun 11, 4:13Â*am, [e-mail] wrote:

Hello,

I am facing the following problem....I am an access newbie:

I have to make a form which has the following controls - DayHours,
NightHours, TotalHours.

Day shifts are defined as shifts from 7am until 6pm
Night shifts are defined as shifts from 6pm until 7am

I have a table with EmployeeID, StartTime, EndTime.

As an example, if exmployee X worked from 1pm until 10pm - that should
count as 6 day work hours and 3 night work hours.

How could I implemet such a system that works out the day and night
work hours based on the shift times???

Many thanks in advance,

Nadav

what is the datatype of the startTime and endTime fields ? datetime ?
long integer?-הסתר טקסט מצוטט-

-הר�ה טקסט מצוטט-

Datetime....- Hide quoted text -

- Show quoted text -

something like this ?
SELECT Table2.employeeId, Table2.name, Table2.startTime,
Table2.endTime, Hour([endtime])-Hour([STARTTIME]) AS timeWork,
Hour("6:00pm")-Hour([startTime]) AS dayWork, (Hour([endtime])-
Hour([STARTTIME]))-(Hour("6:00pm")-Hour([startTime])) AS nightWork
FROM Table2;

[nadav.katz]

On 12 יו×*×™, 13:00, Roger <[e-mail]:

On Jun 11, 7:06Â*am, [e-mail] wrote:

On 11 יו×*×™, 15:57, Roger <[e-mail]:

On Jun 11, 4:13Â*am, [e-mail] wrote:

Hello,

I am facing the following problem....I am an access newbie:

I have to make a form which has the following controls - DayHours,
NightHours, TotalHours.

Day shifts are defined as shifts from 7am until 6pm
Night shifts are defined as shifts from 6pm until 7am

I have a table with EmployeeID, StartTime, EndTime.

As an example, if exmployee X worked from 1pm until 10pm - that should
count as 6 day work hours and 3 night work hours.

How could I implemet such a system that works out the day and night
work hours based on the shift times???

Many thanks in advance,

Nadav

what is the datatype of the startTime and endTime fields ? datetime ?
long integer?-הסתר טקסט מצוטט-

-הר�ה טקסט מצוטט-

Datetime....- Hide quoted text -

- Show quoted text -

something like this ?
SELECT Table2.employeeId, Table2.name, Table2.startTime,
Table2.endTime, Hour([endtime])-Hour([STARTTIME]) AS timeWork,
Hour("6:00pm")-Hour([startTime]) AS dayWork, (Hour([endtime])-
Hour([STARTTIME]))-(Hour("6:00pm")-Hour([startTime])) AS nightWork
FROM Table2;-הסתר טקסט מצוטט-

-הר�ה טקסט מצוטט-

Hi Roger,

Many thanks for your help!

it works well if the end time is midnight.

As an example if someone started to work at 5pm and finished the shift
at 2am the result of nightWork is minus 16 instead of plus 8. :

select Hour("2:00am")-Hour("5:00pm") - (Hour("6:00pm") - Hour
("5:00pm")) as nightWork
Many many thanks in advance!

Nadav