Convert work days to hours

[Yam84]

Hello,

I am trying to calculate leave taken. I track leave by having the users input the days they are taking off. there is a reasonDtFrom and reasonDtTo for dates. I use the dateadd function to determine how
many days that are being taken off, however I need it in hours. I realize I could change my statement to:

HoursOut: (DateDiff("h",[ReasonDetails]![reasonDtFrom],[ReasonDetails]!
[reasonDtTo]))

However that gives me more hours than in the 8 hour work day. For
instance, one of my users is taking 7 days off. it should say 48 (8
hours per day, 6 days) hours have been used, it says 144 (24 hours per
day, 6 days). Is there anyway that I could make this calculate work
days?

 

[PWise]

how about

HoursOut: (DateDiff("d",[ReasonDetails]![reasonDtFrom],[ReasonDetails]!
[reasonDtTo]))*8

but this does not account for weekends

 

[Yam84]

Hi pwise,

Thank you for the reply. This works!

 

[Yam84]

pwise,

I apologize...this is not what I need.

My issue is that I need to be able to record the hours a person uses...for instance a person can elect to take a fraction of the day off. How can I deal with that dynamic?

 

[PWise]

how do you record /store what the person used (took off)?

 

[Yam84]

tblVacationTime

-vacaID- PK Number
-empID - FK Employee, Number
-vacaTimeEarned - Number
-vacaTimeAwardDt - Date

 

[PWise]

this table seems like a table wher you record vacation time comeing to a person, not time that the person took

 

[Yam84]

I am glad you pointed that out This is what I am struggling with currently. I do not have a table representing this information. I used to have a field in the above table that kept track of used time,but was told that was un-needed bc it was a calculated field. I wondered if I was supposed to keep track of each time leave is given and used or could I just overwrite the record each time, judging from your response, I need another table to keep track of used leave?

 

[PWise]

well for one thing you never overwrite records

either you have a table with time taken or store any time taken with in tblVacationTime with a negitive in vacaTimeEarned

 

[Yam84]

OK...so as to not re-invent the wheel, how can I store negative time taken? Would I need another field in the table to account for leave used?

 

[PWise]

no jus enter if the vacaTimeEarned field an negative number let say if on
[tt]
vacid empid vacaTimeAwardDt vacaTimeEarned
1 1 1/31/9 2
2 2 1/31/9 2
3 2 2/5/9 -1
4 1 2/6/9 -1
5 1 2/7/9 -1
[/tt]

 

[PWise]

now if vacaTimeEarned is for days enter vacition taken in hours as
1 hour = .125
2 hours = .25
3 hours = .275
....

 

[Yam84]

Not sure I understood your last post. Are you suggesting that I just enter a negative number when they are using leave?

I ask this because the user never adds leave manually, per se. It is assigned via an update statement that is launched when the user clicks a button.

Partial time may be taken, normally in increments of 4.

 

[PWise]

well: What table are you updateing
whe is ths Sql Command or VBA code

 

[Yam84]

I am updating: vacationTime

Here is the code:

UPDATE Employee INNER JOIN vacationTime ON Employee.empID=vacationTime.empID SET vacationTime.vacaTimeEarned = [vacaTimeEarned]+4, Employee.empRestStatus = False, vacationTime.vacaTimeAwardDt = Date(), vacationTime.empID = [empID]
WHERE (((vacationTime.empID) Not In (SELECT Employee.empID FROM Employee INNER JOIN (Department INNER JOIN DeptTrans ON Department.deptID = DeptTrans.deptID) ON Employee.empID = DeptTrans.empID WHERE (((Department.deptID) In (SELECT DISTINCT Department.deptID FROM ((Department INNER JOIN DeptTrans ON Department.deptID = DeptTrans.deptID) INNER JOIN Employee ON DeptTrans.empID = Employee.empID) INNER JOIN EmpIncidents ON Employee.empID = EmpIncidents.empId WHERE DeptTrans.deptTransDt <= EmpIncidents.incidentDt AND Nz([DeptTrans].[deptTransEndDt],Date())>= EmpIncidents.incidentDt)) AND ((DeptTrans.deptTransDt)>=DateAdd("m",-2,Date())) AND ((Nz([DeptTrans].[deptTransEndDt],Date()))<=Nz([DeptTrans].[deptTransEndDt],Date()))))));

All this does is add a record to the vacationTime table. I figured out it overwrote what I had which really isnt' what I wanted. I wanted to keep a history. I explored the append query, but I keep returning no records at all.

 

[PWise]

insert into tblVacationTime(vacaTimeEarned,empID,vacaTimeAwardDt )
select 4 as vacaTimeEarned ,empID,Date as vacaTimeAwardDt
from Employee inner join ......
costrucet joins to include only emploees that you want

to append records to tblVacationTime