469,622 Members | 1,462 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

End of Month Date after Date add

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
4 3414
missinglinq
3,532 Expert 2GB
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.

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
NeoPa
32,200 Expert Mod 16PB
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
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
missinglinq
3,532 Expert 2GB
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.

Similar topics

6 posts views Thread by Hasanain F. Esmail | last post: by
6 posts views Thread by Ante Perkovic | last post: by
reply views Thread by larry | last post: by
22 posts views Thread by Stan | last post: by
6 posts views Thread by Nkhosinathie | last post: by
3 posts views Thread by One | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.