Calculation for first of a month

[sellis]

I am using a calculation to add 60 days to a date, and then I need the first day of the month following that calculated date. I can get the 60 days no problem. It is getting the first of the month following that date I am having issues with. Any ideas??

 

[Joe4]

Take a look at the last formula under #10 here:
http://www.techrepublic.com/article/10-tips-for-working-with-dates-in-microsoft-access/6135056

You would just replace your date field + 60 in place of all the date references, i.e.:

FirstDay: DateSerial(Year([DateField]+60),Month([DateField]+60)+1,1)

 

[sellis]

Do you have a suggestion to deal with the rare times when the +60 calculation returns a date that is the first of a month and I need to keep that date?

Yeah - that worked perfectly except for the calculations that had already returned the first of the month just because that was the +60 and I need those to stay on the +60 day and not be calculated to the first of the next month. Any ideas there?

 

[Joe4]

We can just add an IIF statement to decide whether or not to add one to the month depending on the date of Date + 60:

FirstDay: DateSerial(Year([DateField]+60),Month([DateField]+60)+IIF(Day([DateField]+60)=1,0,1),1)

 

[sellis]

I love this posting board. You and one other gentleman have solved two problems that I have been working for hours on. I appreciate your help more than you will ever know!!! Thanks soooo much.