Question Include a Prompt to inform the user that an employee is due to retire

[mitz007]

Hi,

I am trying to create a prompt to slert the user that an employee is due to retire. The age in which it should say this is 65 years of age.

As the age of the employee is currently within a Date of Birth format, I am not to sure of how to get this done. The prompt should show 7 months before the employee is due to retire.

I have used something similar for the user to be prompted when someone contract is due to expire, I used the following expression:

=IIf(DateDiff("m",[ContractEndDate],DateAdd("m",6,Date()))<=6,"CONTRACT EXPIRES IN LESS THAN SIX

Any help would be much apreciated.

Many thanks

 

[DCrake]

So Ok how many months are they in 64 years and 5 months?
What date will it be if you add this figure to their date of birth?

is the date diff less than 7 months

Display message.

David

 

[mitz007]

Baiscally the age of the employee should be 64 and 5 months before they turn 65 when a message should display, but as I have the date of birth in a date format i.e. dd/mm/yyyy, I am not to sure how this will work.

 

[DCrake]

Public Function RetirementDue(AnyDOB As Date,RetireAge As Integer,Notice as integer) As Boolean

'This function is a bit more flexible as it can contend with both male and female employees
'Male being 65 Female being 60
'Also you can pass the number of months notice to check for

'Step 1 - Add 65 years to the persons date of birth
'Step 2 - Calculate difference in months between today and retirement date


Dim DtmRetire As Date

DtmRetire = DateAdd("yyyy", RetireAge, AnyDOB)

M = DateDiff("m", Date, DtmRetire)

If M <= Notice Then
  RetirementDue = True
Else
 RetirementDue = False
End If

End Function

Example - Male with 7 months notice

If RetirementDue(Me.Dob,65,7) = True Then
....
End If

Example - Female with 12 months notice

If RetirementDue(Me.DOB,60,12) = True Then
...
End If



David

 

[mitz007]

This may sound really silly, but where shall I include this script? Shall I create a text box and place it within it?

Sorry I'm not the best with databases.

 

[DCrake]

Create a new module and save the public function inside it.

Then in your form when you pick an employee us the forms On Current Event
to call the function

as explained earlier.

If RetirementDue(Me.TxtDOB,65,7) = True Then
    Msgbox "Employee is due to retire in less than seven months"
Endif

Where Me.TxtDOB is the name of the control on the form that is bound to the persons date of birth field in the table.

David

 

[mitz007]

Thanks David,

Im still a little unsure,

I've created a new module and saved the public function inside it.

I am unsure exactly what to do in terms of using the On Current Event to call the function as when I include the code you have given:

If RetirementDue(Me.TxtDOB,65,7) = True Then
Msgbox "Employee is due to retire in less than seven months"
Endif

I get a debug message which highlights "RetirementDue" within the code.