End of Month Date after Date add

[mattgray89]

I am new to Access and trying to wrap my head around VBA. I need to first add 17 months to the field "1stQualLastSat" and to have the output date be at the end of that month. Is this possible? A dumbed down answer is greatly appreciated, thank you all so much.

[missinglinq]

First off, you really need to change the name of your field

1stQualLastSat

to something else, like

FirstQualLastSat

Access doesn't like object names that start with a digit! When you go into the code module behind a form you'll find that Access will change it by adding Ctl in front of the leading digit, and you'll have to refer to like that in code, which is very confusing.

You also didn't say where you want to do this calculation. I've given you an example here using the AfterUpdate event of the field FirstQualLastSat.

Private Sub FirstQualLastSat_AfterUpdate()
  Me.CalculatedField = DateSerial(Year(DateAdd("m", 17, Me.FirstQualLastSat)), Month(DateAdd("m", 17, Me.FirstQualLastSat)) + 1, 0)
End Sub

Now, when you enter a date in FirstQualLastSat the control CalculatedField will be populated with the last day of the 17th month after your date.

Welcome to Bytes!

Linq ;0)>

[NeoPa]

For getting the last day of a month I usually use :

Me.CalculatedField = CDate(Format(DateAdd("m", 18, Me.FirstQualLastSat), "1 mmm yyyy")) - 1

[mattgray89]

Sorry it took so long to get back on and read your replies. This worked perfectly, thank you both so much for the help and the warm reception.

[missinglinq]

Glad we could help!

Linq ;0)>