Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 09, 2021

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

  • isladogs's avatar
    isladogs
    Nov 10, 2021
    PMFJI
    Try DateSerial(Year(Date()),12,31)
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    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.

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      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.
      • George_Hepworth's avatar
        George_Hepworth
        Silver Contributor

        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.

Resources