DateAdd returns bogus valueUpload ImagesLink 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: Private Sub MonthAddBtn_Click()
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:
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
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
