SOLVED

Formula for End of Current Year in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2941527%22%20slang%3D%22en-US%22%3EFormula%20for%20End%20of%20Current%20Year%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2941527%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20I%20have%20a%20form%20and%20fills%20in%20fields%20based%20on%20which%20button%20I%20press.%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20buttons%20fills%20fields%20as%20below%20with%20current%20date%20and%20I%20am%20having%20an%20issue%20populating%20the%20field%20that%20is%20the%20end%20of%20the%20year%20(Me.txtDateTo).%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20where%20I%20am%20wrong.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20cmdyear_Click()%3CBR%20%2F%3EMe.txtdatefrom%20%3D%20Date%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B'doesnt%20work%20in%20VBA%3A%20%3CSTRONG%3EMe.txtDateTo%3C%2FSTRONG%3E%20%3D%20DATE(YEAR(TODAY())%2C12%2C31)%3CBR%20%2F%3E%3CBR%20%2F%3E'%26nbsp%3B%20doesnt%20work%3A%20%3CSTRONG%3EMe.txtDateTo%3C%2FSTRONG%3E%20%3D%20DateAdd(%22yyyy%22%2C%201%2C%20Date)%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2941527%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eaccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2941585%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20End%20of%20Current%20Year%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2941585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20does%20it%20mean%20to%20say%20%22it%20doesn't%20work%22%3F%3C%2FP%3E%3CP%3EWhat%20are%20you%20expecting%20the%20result%20to%20be%3F%20What%20is%20the%20actual%20result%3F%20How%20are%20they%20different%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESometimes%20what%20is%20clear%20to%20the%20person%20looking%20at%20the%20Access%20database%20is%20not%20clear%20to%20anyone%20who%20can't%20see%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

 

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

@Tony2021 

 

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.

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.

@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 (Frequent Contributor)
Solution
PMFJI
Try DateSerial(Year(Date()),12,31)
perfect. thank you very much!
You're welcome!