Hi..
In my table Name,DOB(Date of Birth),DOA(Date of appointment) field are there
now I want DOR(Date of Retirement) after 60 yers if DOB is 1st day of month
DOR should come last day of previews month otherwise last day of month after
60 years and I wnat qualifing service like No of days, No of Month & No of
years from DOA field-DOR field. so please how should write function in MS.Access

Mohamed Mohiddin
Officer

__________________
Mohamed Mohiddin
Officer

Hi Mohamed,

This should get your date of retirement. Just use the DateDiff function to calculate your total days, months, years:

Sub RetirmentDate(datDateOfBirth As Date)

    Dim datRetirementFlag As Date
    Dim datDateOfRetirement As Date

    datRetirementFlag = DateAdd("yyyy", 60, datDateOfBirth)

    If (datDateOfBirth = FirstDayofMonth(datDateOfBirth)) Then
        datDateOfRetirement = LastDayofPreviousMonth(datRetirementFlag)
    Else
        datDateOfRetirement = LastDayofMonth(datRetirementFlag)
    End If

    Debug.Print datDateOfRetirement

End Sub

Function FirstDayofMonth(datDOB As Date)
    FirstDayofMonth = DateAdd("d", 1 - DatePart("d", datDOB), datDOB)
End Function

Function LastDayofPreviousMonth(datRF As Date)
  LastDayofPreviousMonth = (DateAdd("d", 1 - DatePart("d", datRF), datRF)) - 1
End Function

Function LastDayofMonth(datRF As Date)
  LastDayofMonth = DateSerial(DatePart("yyyy", datRF), DatePart("m", datRF) + 1, 1) - 1
End Function

HTH,

Bob

Here's an alternate way of calculating the beginning and ending days of the month for use with the above solution.

Public Function BegOfMonth(DateIn As Date) As Date
BegOfMonth = DateAdd("d", -(Day(DateIn) - 1), DateIn)
End Function

Public Function EndofMonth(DateIn As Date) As Date
EndofMonth = DateAdd("d", -1, (DateAdd("m", 1, BegOfMonth(DateIn))))
End Function

[email protected]