If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vacation Accural
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. |
#2
|
|||
|
|||
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 -- Duane Hookom MS Access MVP "Jannie" wrote in message ... 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. |
#3
|
|||
|
|||
Thank you Duane. I will try this. If I need further help, I'll write back .
Thank you so very much. Jannie "Duane Hookom" wrote: 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 -- Duane Hookom MS Access MVP "Jannie" wrote in message ... 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. |
#4
|
|||
|
|||
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" wrote: 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 -- Duane Hookom MS Access MVP "Jannie" wrote in message ... 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. |
#5
|
|||
|
|||
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 -- Duane Hookom MS Access MVP -- "Jannie" wrote in message ... 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" wrote: 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 -- Duane Hookom MS Access MVP "Jannie" wrote in message ... 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. |
#6
|
|||
|
|||
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" wrote: 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 -- Duane Hookom MS Access MVP -- "Jannie" wrote in message ... 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" wrote: 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 -- Duane Hookom MS Access MVP "Jannie" wrote in message ... 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. |
#7
|
|||
|
|||
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. -- Duane Hookom MS Access MVP "Jannie" wrote in message ... 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" wrote: 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 -- Duane Hookom MS Access MVP -- "Jannie" wrote in message ... 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" wrote: 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 -- Duane Hookom MS Access MVP "Jannie" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How can I calculate Vacation Time earned based on length of emplo. | Kim | General Discussion | 2 | March 15th, 2005 08:04 PM |
Auto or Vacation Reply? | Jo | General Discussion | 2 | July 21st, 2004 10:22 PM |
Vacation Reply | Matt Olson | Outlook Express | 3 | July 18th, 2004 06:41 PM |
Vacation Accrual | Phyllis | Worksheet Functions | 1 | March 24th, 2004 06:59 PM |
Function to figure vacation accrual | Liz | Worksheet Functions | 3 | November 13th, 2003 11:31 PM |