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.

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

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.

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?

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

Does no one have any answer to this for me?

Thanks for ANY help of writing this calculation!

Tom

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

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

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.

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

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

Tom

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)

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

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.

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