Output First of Month Following 60 Days From a Stored Date in an Access qry

Keywords:ms  access 


I have a date field in access where I have to calculate the first of the month following 60 days from that date. For example my date is 12/12/2017. I need to output 3/1/2018, First of the month following 60 days from 12/12/2017. I know how to use the DateAdd function to get my 60 days, but am confused as to how to output the first of the month following those 60 days.

Thanks, Mark

2 Answers: 

There are multiple ways to do this. One way using DATEADD():

SELECT DATEADD("d", -"d"(DATEADD(MONTH, 1, DATEADD("d", 60, '2017/12/12')))+1,
        DATEADD("m", 1, DATEADD("d", 60, '2017/12/12')))

If you have Access 2013+ you can use EOMONTH()

SELECT DATEADD("d", 1, EOMONTH(DATEADD("d", 60, '2017/12/12')))

Use this expression:

DateThreeFirst: DateAdd("m", 3, DateSerial(Year([YourDateField]), Month([YourDateField]), 1))