End of next month due date

[rdwest01]

I would like to create a query that takes any date in the current month and returns a date that is the last day of the next month.

eg. I enter the 10 Sep 09, and the return date would be 31 Oct 09. Regardless of the initital date, the return date will always be the last day of the following month.

Using Access 2003.

 

[boblarson]

You can use this in a query:

LastDateOfNextMonth: DateSerial(Year(Date()), Month(Date()) + 2, 0)

What that does is create the date using the current year, the current month + 2 months and then day 0 which is the last day of the previous month. So, since we add two months and then get the last day of the previous month it actually ends up being the last day of next month. Try it and you'll see.

 

[rdwest01]

You can use this in a query:

LastDateOfNextMonth: DateSerial(Year(Date()), Month(Date()) + 2, 0)

What that does is create the date using the current year, the current month + 2 months and then day 0 which is the last day of the previous month. So, since we add two months and then get the last day of the previous month it actually ends up being the last day of next month. Try it and you'll see.

Awesome, works like a charm. Thank-you

 

[rdwest01]

You can use this in a query:

LastDateOfNextMonth: DateSerial(Year(Date()), Month(Date()) + 2, 0)

What that does is create the date using the current year, the current month + 2 months and then day 0 which is the last day of the previous month. So, since we add two months and then get the last day of the previous month it actually ends up being the last day of next month. Try it and you'll see.

Just a quick observation. For some reason, when you enter 31 Aug 09, the result is 31 Oct 09 an d not 30 Sep 09. Is this an anomoly

 

[gemma-the-husky]

this should definitely fix it then - this goes to the first day of two months hence, then comes back a day

LastDateOfNextMonth: DateSerial(Year(Date()), Month(Date()) + 2, 1) - 1

 

[boblarson]

Just a quick observation. For some reason, when you enter 31 Aug 09, the result is 31 Oct 09 an d not 30 Sep 09. Is this an anomoly

Shouldn't be. It works fine for me when entering 08/31/2009 (remember to put the parts in the right spot). It gives me 09/30/2009 which is correct.

 

[boblarson]

this should definitely fix it then - this goes to the first day of two months hence, then comes back a day

LastDateOfNextMonth: DateSerial(Year(Date()), Month(Date()) + 2, 1) - 1

Mine works too as Day 0 is subtracting one from the end of the month, but it probably has to do with getting the wrong date format (U.S. over U.K.) that Access is expecting (remember the nice stuff that MS did which requires U.S. date formats for certain things - which is a pain for those outside the U.S.

 

[rdwest01]

Mine works too as Day 0 is subtracting one from the end of the month, but it probably has to do with getting the wrong date format (U.S. over U.K.) that Access is expecting (remember the nice stuff that MS did which requires U.S. date formats for certain things - which is a pain for those outside the U.S.

I'll check both ideas out. I remember the format change. And let you know.

 

[boblarson]

I'll check both ideas out. I remember the format change. And let you know.

Also, if you changed my formula in anyway (to add a field, etc) can you post it here so we can make sure it is right? The one I posted would bring back the date based on today's date, so based on your response I would say you probably changed it a bit.