By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

End of Month Date after Date add

P: 2
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.
Apr 28 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,532
First off, you really need to change the name of your field


to something else, like


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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub FirstQualLastSat_AfterUpdate()
  2.   Me.CalculatedField = DateSerial(Year(DateAdd("m", 17, Me.FirstQualLastSat)), Month(DateAdd("m", 17, Me.FirstQualLastSat)) + 1, 0)
  3. 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)>
Apr 28 '09 #2

Expert Mod 15k+
P: 31,709
For getting the last day of a month I usually use :
Expand|Select|Wrap|Line Numbers
  1. Me.CalculatedField = CDate(Format(DateAdd("m", 18, Me.FirstQualLastSat), "1 mmm yyyy")) - 1
Apr 29 '09 #3

P: 2
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.
May 3 '09 #4

Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
May 3 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.