none
DateSerial Function not returning correct date for end of month RRS feed

  • Question

  • Case 1390861292

     https://support.microsoft.com/en-us/help/210604/functions-for-calculating-and-for-displaying-date-time-values-in-acces

    Using Access 2016 and the following formula in a text field on a form to return the date at the end of the month:

    DateSerial(Year(Date()), Month(Date()) + 1, 0)

    I am getting July 30, 2017 but there are 31 days in July.

    Sarah C. on "Live Chat" suggested I go to Pro Support, but that appears to be a pay per use service.  I need support for what appears to be a bug in your program.  Please help.

    Wednesday, July 5, 2017 8:51 PM

All replies

  • I have also tried: 

    DateAdd("m",1,DateSerial(Year([MyDate]),Month([MyDate]),1))-1

    from this post:

    //social.msdn.microsoft.com/Forums/en-US/bfaccea9-e6e9-48f1-ade5-d99d0bc75995/creating-end-of-month-dates?forum=accessdev

    and I am still getting July 30, 2017 and NOT July 31, 2017


    • Edited by Opal_43 Wednesday, July 5, 2017 8:58 PM missed something
    Wednesday, July 5, 2017 8:57 PM
  • Interesting... I gave it a quick try in the Immediate window and got the following:

    Did you try it in the Immediate window too?


    • Edited by .theDBguy Wednesday, July 5, 2017 9:19 PM
    Wednesday, July 5, 2017 9:18 PM
  • DateAdd("m",1,DateSerial(Year([MyDate]),Month([MyDate]),1))-1

    Hi Opal_43,

    For me this works:   result =  31-jul-2017, when MyDate = Date().

    Imb.

    Wednesday, July 5, 2017 9:22 PM
  • ciao Opal_43,

    another option should be :

     eofDate:dateadd("m",1,date)-day(date)

    or this :

    =DateAdd("m";1;Date())-Day(Date())

    as recordSource of unbound textBox control.

    Ciao!

    Sandro.

    Thursday, July 6, 2017 5:40 AM
  • Thank you, everyone, for your replies.  It's working today.  Just weird that it didn't work yesterday.
    Thursday, July 6, 2017 1:30 PM
  • Thank you, everyone, for your replies.  It's working today.  Just weird that it didn't work yesterday.

    Hi,

    Glad to hear it's working now. Did you reboot your machine?

    Thursday, July 6, 2017 2:44 PM
  • Hi Opal,

    I am glad your issue has been resolved, and I would suggest you mark the helpful reply as answer to close this thread.

    Regards,

    Tony


    Help each other

    Wednesday, July 12, 2017 7:44 AM