Dates

[patricespencer]

I hope someone can help me. I need to calculate a Normal Retirement Date. That is the date that you are 65.

Also, when calculating this date if you are born <=16 of the month then the date needs to be the first of that month (ex. dob=7/12/57 the NRD would be 7/1/2022) and if you are born after the 16th of the month, the date needs to be the 1st of the next month (ex. dob=7/23/57 the NRD would be 8/1/2022).

Does anyone have any ideas how I can do this?

Thanks.

 

[D-Fresh]

Sure use this function...

Public Function GetNRD(DOB As Date) As Date
Dim TempDate As Date

TempDate = DateAdd("yyyy", 65, DOB)
If Day(DOB) <= 16 Then
GetNRD = CDate(Month(DOB) & "/1/" & Year(TempDate))
Else
GetNRD = CDate((Month(DOB) + 1) & "/1/" & Year(TempDate))
End If

End Function

Hope that helps.

Doug

 

[patricespencer]

Thanks for the quick answer. Now I have another question...how will this handle Dec/Jan. If the employee was born 12/23/1957 then his NRD will be 01/01/2023. Will this fuction handle something like this.

Also, I am very new to programming in Access, where do I put this function.

Thanks for all your help.

 

[vangogh228]

I'd like to get some followup on this issue as well. The DEC/JAN issue here, seems to me, would be a problem.

My client uses strictly the first of the month FOLLOWING the 65th birthday.

Help?

 

[vangogh228]

Anyone? Anyone? This answer would be a HUGE help to me right now. THANKS!!

 

[vangogh228]

Does no one have any answer to this for me?

Thanks for ANY help of writing this calculation!

Tom

 

[Pat Hartman]

The solution is to work with the entire date rather than just the month part.

Public Function GetNRD(DOB As Date) As Date
Dim TempDate As Date

TempDate = DateAdd("yyyy", 65, DOB)
If Day(DOB) <= 16 Then
GetNRD = CDate(Month(TempDate) & "/1/" & Year(TempDate))
Else
GetNRD = CDate(DateAdd("m",1,TempDate) & "/1/" & Year(DateAdd("m",1,TempDate)))
End If

End Function

 

[vangogh228]

Pat and D-Fresh. et al:

I think I understand where we're headed here... but I don't understand GetNRD.

If I simply want the first of the month following the 65th birthday, would it be:

Public Function GetNRD(DOB As Date) As Date
Dim TempDate As Date

TempDate = DateAdd("yyyy", 65, DOB)
GetNRD = CDate(DateAdd("m",1,TempDate) & "/1/" & Year(DateAdd("m",1,TempDate)))
End If

End Function

???

Thanks! Tom

 

[Pat Hartman]

This statement adds 65 years to the DOB:

TempDate = DateAdd("yyyy", 65, DOB)

This statement adds 1 to the current month and concatenates the result with day and year. The reason that the calculation is repeated in the year component is because if you add 1 to Dec, you get Jan PLUS the year changes. So, in case that was the situation, the calculation needs to be done also to extract the correct year.

GetNRD = CDate(Month(DateAdd("m",1,TempDate)) & "/1/" & Year(DateAdd("m",1,TempDate)))

Sorry, the Month function was missing from the first part of the statement.

 

[vangogh228]

OK... I am just not getting this.

I have a field called "birthdate" and am trying to create one called "Normal Retirement Date" with the formula. I am not sure how to create the field using the code or how to name the field to be updated.

I am still hung up, I think, on Excel formula syntax conventions and I just am not getting this. If someone could give me the play-by-play on doing this, I would be GREATLY indebted!!

Thanks!!!
Tom

 

[vangogh228]

I am still struggling with this. Thanks for any who can clear this up for me.

Tom

 

[Pat Hartman]

Open a code module and add the function to it. Then in your query you can refer to the function to calculate the retirement date:

Select fld1, fld2, GetNRD(DOB) As RetirementDate
From YourTable;

You can also use the function as the controlsource for a control.

=GetNRD(DOB)

 

[vangogh228]

Pat:

I am obviously missing something significant here. I do not have a query related to this. All I have is a form field called "Birthdate" and a form field in which I want to calculate the retirement date, which is to be called "Normal Retirement Date." The date should be the first of the month following the 65th birthday.

I don't understand what "GetNRD(DOB)" is. Also, I did put the code in a module but the field just stays blank. I apologize... I understand a little VBA, but this is beyond me.

Tom

 

[Pat Hartman]

You can also use the function as the controlsource for a control.
=GetNRD(DOB)

So, put
=GetNRD(DOB)
in the controlsource of the control that you want to show the retirement date.

 

[vangogh228]

OK... I would like to use VBA code, but I have little understanding. I found the DateSerial function, and put the following in an unbound text box:

=DateSerial(Year([Birthdate])+65,Month([BirthDate])+1,1)

It works.

Tom