Vacation Accural

[Jannie]

I need help in Access if someone could help me write the formula:
An employee starts accuring vacation from the Date of Hire (conditions: if
he starts work before the 15th of the month, you count that month, if he
starts work after the 15th of the month you don't.) Also, You are eligible
for vacation after 6 months of service. I could sure some help on this one.
Thank you.

 

[Duane Hookom]

I would probably open a new, blank module and immediately save it as
"modBusinessRules". It is best to keep all the calculations in a single
place so that WHEN they change, they are easy to find and update. Then
create your function:

Public Function AccruedVacation(pdatHireDate as Date) as Double
'don't know if you count vacation in full days or hours or what
Dim dblAccruedVacation as Double
dblAccruedVacation = 0
If Day(pdatHireDate) < 15 Then
dblAccruedVacation = ....
End If
'other code as required
AccruedVacation = dblAccruedVacation
End Function

 

[Jannie]

Thank you Duane. I will try this. If I need further help, I'll write back .
Thank you so very much.
Jannie

 

[Jannie]

Duane,
Here is more of what I need this to say. I want to put this in a cell as an
expression I think. Vacation starts accuring from the date of hire. If you
hire in before the 15th of the month, that month would be counted in the
accural. You are eligible after 6months of service to take vacation.
Vacation is accured at 6.67 hours a month until you have been there 5 years.
Then again the accural changes at that point to 10.00 hours a month. If you
hired on the 15th or sooner then you would accure at the 10 hr rate for that
month forward else you would begin the next month. I have a cell that says
Emp Hire Date where I put the date of hire. Then a cell for number of days
accured. which would be this formula minus the number of days taken which
is an input cell called Vacation Days Taken. Then there is a cell that says
Vacation Days availalble. That cell holds the result of Accured days minus
the days taken. Hope this explains this better. I'm not really good at
this so even though it may seem harder for you, I'm trying to write the
expression in the cell so I can learn easier for me. Any help you can give
that would be great. The formula should not be that hard and maybe I am
making it harder on myself than I need to.

 

[Duane Hookom]

I would create a function that returns the total accrued hrs. You could then
use the function as a calculated column in a query or anywhere else a
function is valid.
Function GetAccruedHrs(pdatStart As Date) As Double
Dim dblHours As Double
Dim intMonths As Integer
'date to start accruing vacation from
Dim datFromDate As Date

'6.67 hours a month
Dim dblInitRate As Double
dblInitRate = 6.67
'the accural changes to 10.00 hrs a month
Dim dblLaterRate As Double
dblLaterRate = 10

'If you hire in before the 15th of the month, _
that month would be counted in theaccural
If Day(pdatStart) < 15 Then
datFromDate = DateSerial(Year(pdatStart), _
Month(pdatStart), 1)
Else
datFromDate = DateSerial(Year(pdatStart), _
Month(pdatStart) + 1, 1)
End If
'You are eligible after 6months of service
intMonths = DateDiff("m", datFromDate, Date)
Select Case intMonths
Case Is <= 6
GetAccruedHrs = 0
Case Is <= 60
GetAccruedHrs = intMonths * dblInitRate
Case Else
GetAccruedHrs = 60 * dblInitRate + _
(intMonths - 60) * dblLaterRate
End Select
End Functi

 

[Jannie]

Duane,
Well I tried to write this exactly like you said but am having no luck maye
I'm not putting all the ' where I should. I dunno. I had an access guy here
look at it for me and he said it appears to him that there is some code that
is missing before and after this to help me get the correct answer. Use this
example for a person hiring in on the 1/15/2005. I put that in a field
called "Emp Hire Date". Then I have a field that is called "Vacation Days
Accured". And that is where I want the formula to be. (The one your helping
me with.) But exactly where in properties do I put that code. Also, I need
to be sure I say it exactly right so it will work. Then I have another field
called "Vacation Days Used". That takes the result of the formula cell and
subtracts the days used and puts it into a field. called "Vacation Days
Available".

We have some employees that hired in at the 6.67 hrs per month and some that
hired in at the 10.00 hrs per month rate. Then there is the factor that some
of the folks that originally hired in at 6.67 hrs per month after 5 years of
service then moved into the 10.00 hrs per month accural rate. I can create a
field that says which rate they are currently at like "Rate" and enter 1 for
the 6.67 and 2 for the 10.00 rate. That is no problem, but would have to
incorporate that in the formula as well. If you follow me.

I am almost done with this project and I have this and one other big formula
I will need help with to get this done. I appreciate all your doing to help
me.

Sincerely,
Jannie

 

[Duane Hookom]

Did you create a new, blank module as I suggested in my first posting? Add
this function to the module. You can then use the function like you would
any other function such as Format() or DateAdd() or other. To get the
accrued hours in a query, you would create a column in your query like:
VacHrsAccrued: GetAccruedHrs([Emp Hire Date])
If you have different accrual rates than you will have to store some value
in your employee table and modify the function to take this value into
consideration.