SOLVED

# Formula for End of Current Year in VBA

Regular Contributor

# Formula for End of Current Year in VBA

Hello, I have a form and fills in fields based on which button I press.

One of the buttons fills fields as below with current date and I am having an issue populating the field that is the end of the year (Me.txtDateTo).

Please let me know where I am wrong.

Thank you

Private Sub cmdyear_Click()
Me.txtdatefrom = Date

'doesnt work in VBA: Me.txtDateTo = DATE(YEAR(TODAY()),12,31)

'  doesnt work: Me.txtDateTo = DateAdd("yyyy", 1, Date)

End Sub

6 Replies

# Re: Formula for End of Current Year in VBA

What does it mean to say "it doesn't work"?

What are you expecting the result to be? What is the actual result? How are they different?

Sometimes what is clear to the person looking at the Access database is not clear to anyone who can't see it.

# Re: Formula for End of Current Year in VBA

HI George, sorry about that. I read the question again and I agree I could have added more info. What I am looking for is a line of code that will return the last day of the year for Me.txtDateTo. The examples I provided did not work. the one that has DATE(YEAR(TODAY( in the code is a formula that works in excel but not VBA.

thank you. Let me know if its not clear.

# Re: Formula for End of Current Year in VBA

@Tony2021 "...The examples I provided did not work. "

Again, what does that mean? Errors? Wrong values? Describe the behavior, not the outcome. That's where we can spot clues to what might be wrong.

best response confirmed by Tony2021 (Regular Contributor)
Solution

# Re: Formula for End of Current Year in VBA

PMFJI
Try DateSerial(Year(Date()),12,31)

# Re: Formula for End of Current Year in VBA

perfect. thank you very much!

You're welcome!