ms access 2003- calculating dates on last record values

[davesmart]

Hi,
My employer is wanted to track employee evaluations. There are 2 types of evaluations - MidEval and FinishEval. The first MidEval is calculated 6 months from the HireDate. The FinishEval is 12 months from the HireDate. I am ok with these calculations. But I need help with the next. I need the next MidEval to be 12 months from the last MidEval and I need the next FinishEval to be 12 months from the last Finish Eval.

I plan on doing the calculations within an If statement to determine if the employee is within the first year of employment or not, in order to know which calculation to use.

Here's the tables and fields I have it setup so far.

tblEmployee
-EmpID
-EmpFName
-EmpLName
-EmpHireDate

tblEvaluation
-EvalType (a lookup combo box giving the option of MidEval or FinishEval)
-NextEvalDate (based on the type selected the calculation will display when the next evaluation is due)
-EvalCompleted (a check box to identify that the eval has been completed)
-CompletedDate (a text box manually entered the date the eval was completed)
-EmpID (relationship linked to tblEmployee)

I have a form for Employee and a subform for Evaluation which are based on queries. The forms are to allow the user to have a point for data entry.

Please let me know if more info is needed or if I need to explain again. Your help is greatly appreciated.
Dave

 

[ZiggyS1]

is this a recurring cycle or is there an End to the evals? I'm just picturing you would add days to the dates where applicable to determine the next date... Hiredate + 180

I got confused with this....

" I need the next MidEval to be 12 months from the last MidEval and I need the next FinishEval to be 12 months from the last Finish Eval.

 

[davesmart]

Hi, thanks for the reply.

this will be reoccuring with no end date (until the employee retires)

I'm looking to use the hire date for the first evaluation evaluations only, but to use hire date for every other one would mean I'd have to code in every year. That's why I was hoping to add 12 months to the last EvalDate.

the first "MidEval" = "HireDate" + 6 months
but every "MidEval" after that should be a year after the last. This is just to create a 6 month buffer between the MidEval and the FinishEval.

Now when I click the cmdAddEval button it will jump to a new record on the tblEvaluation. I would then have the user select the EvalType and based on the selection (OnChange) (MidEval or FinishEval) the EvalDate would be populated with the appropriate date.

***************************************
Example of what I see the process being
EmpID = 12345
HireDate = Jan 20, 2010
User click cmdAddEval
User selects EvalType "MidEval" or "FinishEval"
EvalDate = July 20, 2010 (hire date + 6 months) Where EvalType = "MidEval"
EvalDate = Jan 20, 2011 (hire date + 12 months) Where EvalType = "FinishEval"

User click cmdAddEval
User selects EvalType "MidEval" or "FinishEval"
EvalDate = July 20, 2011 (EvalDate.LastRecord + 12 months) Where EvalType = MidEval
FinishEval = Jan 20, 2012 (EvalDate.LastRecord + 12 months) Where EvalType = FinishEval
***************************************
I believe this would allow me to use this tool for YEARS to come without having to code the specific years. Please let me know if this is achievable or if you have another way of doing it.

Thanks,
Dave