Calculate date but set value to end of the year

[tagteam]

I am trying to calculate when the next test date is. It is 10 years from the last test but not until the end of whatever year it is in.

So if the date is 5/1/02 then the next test is not due until 12/31/12. I have used this code

=DateSerial(Year([LastCoilTest])+10,Month([LastCoilTest]),Day([LastCoilTest]))

to get the 10 years added but I am not sure how to get it to go to the end of that year.
Thanks

[mrojas]

Store the results of your code above into a variable, say dteTheYear.
Use the DatePart function to get the year, and then store into another a new variable, say dteEndOfYear the new date; "12/31/" & dteTheYear
Use dteEndOfYear as needed

[tagteam]

Sorry I might need a little more help on this.

Should i create this variable in a code module? I am not very good with VBA.
would it be
Private sub endyeardate()
Dim strTestDate As String
Dim strEndYear As String
Dim newTestDate as Date
Test Date =DateSerial(Year([LastCoilTest])+10,Month([LastCoilTest]),Day([LastCoilTest]))
EndYear = DatePart ("yyyy", strTestDate)
newTestDate = "12/31" & strTestDate

End Sub

Something like that. Sorry, kind of making it up based on what I know, or rather what I don't know. I would then have the form control to equal the value strTestDate.

[mrojas]

As to where to place this code, it depends how you're applying the "calculate" end of the year.

Let's say you have a form with a button labeled "Calculate Next Test".

Then, in the OnClick event of this button, insert the code as shown below:

Private sub btnCalculate()
Dim dteTestDate as date
Dim str10Years as string
dim dteEndof10Years as date

dteTestDate=DateSerial(Year([LastCoilTest])+10,Month([LastCoilTest]),Day([LastCoilTest]))
str10Years=DatePart ("yyyy", dteTestDate)
dteEndof10Year= "12/31/" & str10Years
End Sub