Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

 Home > Forums > Access
Access Forum
Gary Becker
3 months ago
I have a simple loop. I want to add 1 month to a date and each time it runs through the loop. Here is my code:

Dim CurMonth As Date
Dim X As Long

StatusBox = ""
CurMonth = #1/1/2023#

Do
X = X + 1
Status "Current Month is " & CurMonth
CurMonth = CurMonth + DateAdd("m", 1, CurMonth)
Loop Until X = 5
End Sub

And it returns funny values.
 Gary Becker 3 months ago
 Adam Schwanz 3 months ago It can be helpful to write out what you are actually telling the code to do instead of just using variables sometimes. This line here CurMonth = CurMonth + DateAdd("m", 1, CurMonth) CurMonth is 1/1/2023 so we have 1/1/2023 + DateAdd("m", 1, 1/1/2023) which turns into 1/1/2023 + 2/1/2023 See the problem? ;). In this case just do CurMonth = DateAdd("m",1,CurMonth)
 Brent Davis 3 months ago I have watched the missing months video a million times and i cannot figure out how to get my status box to show only the end dates for each month. I am trying to do this with VBA: Private Sub MonthListBtn_Click()     Dim StartDate As Date, X As Long          StartDate = DateSerial(Year(DATE), Month(DATE), 0)          StatusBox = ""     Do     Status "Current Month is " & StartDate     StartDate = DateAdd("m", 1, StartDate)     X = X + 1     Loop Until X = 12 End Sub It turns out like this: Current Month is 7/29/2024 Current Month is 6/29/2024 Current Month is 5/29/2024 Current Month is 4/29/2024 Current Month is 3/29/2024 Current Month is 2/29/2024 Current Month is 1/30/2024 Current Month is 12/30/2023 Current Month is 11/30/2023 Current Month is 10/30/2023 Current Month is 9/30/2023 Current Month is 8/31/2023 I want each date to be the last day of each month; 1-31-2023, 2-28-2023, 3-31-2023, etc. Any assistance is appreciated. Thanks.
 Kevin Robertson 3 months ago Try this: ```Private Sub MonthListBtn_Click()     Dim X As Long     StatusBox = ""          For X = 1 To 12         Status "Current Month is: " & DateSerial(Year(Date), Month(Date) + X, 0)     Next End Sub```
 Gary Becker 3 months ago Richard's EOMonth TechHelp video from about 10 days ago might also give you some ideas.
 Kevin Robertson 3 months ago Here's a link to the video Gary mentioned: Access EOMONTH
 Brent Davis 3 months ago Gentlemen, thanks for all the assistance!  You guys pointed me in the right direction and after a couple of hours working through it, I  was able to get it closer to what I Invision. A couple more steps and it should be complete. Having to work through it and finally seeing the outcome you Invision is really cool!!! Thanks

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Start a NEW Conversation

Subscribe to Access Forum