End of the Month

[Sebsboutique]

Hi all,

I have had a look around in here for an answer that works to this, but i seem to be struggling a bit. Probably the simplest thing in the world!!

I need to round up a date to the end of the month...

I have a form that displays a tool. This form launches another form that allows our engineers to update tooling inspection dates etc...

So in the Maintenance form i have a Date of inspection (usually just "Date()") and an interval text field for the number of months until the next inspection.

I have a "Calculate" button that needs to take the 'Date of Inspection' field and add the amount of months in the 'Interval' field, then round up to the end of that month. This date is then added to a 'Due Date' field in the form.

Currently i am rocking this code on my button, It does add the interval months correctly, but it doesn't seem to do the round up bit!

Me.DueDate = DateAdd("m", [Interval], [DateOfInspection] + 1) - 1

I appreciate that this is probably a very simple problem, but any help would be great!

Many thanks in advance,
Seb

 

[plog]

The +1 needs to go to the interval not the dateofinspection.

 

[moke123]

To get the last day of the month you would use the zero day of the next month.

Dateserial(Year(dueDate),Month(dueDate)+1,0)

 

[xavier.batlle]

Attached a database with an example using @moke123 suggestion: