Thanks for the help. i will give it a try. Thanks for the advice on the
variables too. I am new to VBA, so it helps to know info like that.
"John Spencer" wrote:
> Using DATE as a variable is a BAD choice. Date is a function that returns the
> current system date. I would recommend that you change the name of the
> variable to dteDate or TheDate or some other variation.
>
> Instead of using DateSerial function try using DateAdd function. It will come
> closer, but it still has problems with the last day of the month if the last
> day of the base month is less than the last day of the result month. If the
> last day of the base month is greater than or equal to the last day of the
> result month the result will be correct.
>
> DateAdd("M",1,DATE)
>
> DateAdd("M",-1,DATE)
>
> You could always test to see if you were on the last day of the month and
> adjust the result automatically.
>
> IF Day(DateAdd("d",1,TheDate) = 1 Then
> TheDate = DateSerial(Year(TheDate),Month(TheDate)+1+1,0)
> Else
> TheDate = DateSerial(Year(TheDate),Month(TheDate)+ 1,Day(TheDate))
> End if
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> lbbeurmann wrote:
> > The code in my first post is for the click of the the command button to move
> > forward. Here is what I am using for the command button to go backward:
> >
> > If DATEOPTION.Value = 3 Then
> > DATE = DateSerial(Year(DATE), Month(DATE) - 1, Day(DATE))
> > Me.DATE = CStr(FormatDateTime([DATE], [vbShortDate]) & " 23:59:00")
> > End If
> >
> > Thanks again.
> >
> > L.B.
> >
> > "lbbeurmann" wrote:
> >
> >> I have an access form with a variety of ways to select dates in a text box
> >> via 2 command buttons for moving the date forward and backward. Using an
> >> option group I allow the user to select either day, week, month, or year to
> >> move the date forward or backward in those increments. However, I am having
> >> an issue when the month option is selected and the date in the text box falls
> >> on the end of the month. My desired result would be to move, for example,
> >> from DEC 31, to NOV 30 if using the back button (right now it moves from DEC
> >> 31 to DEC 1) or to move from JAN 31 to FEB 28 using the forward button (right
> >> now it moves from JAN 31 to MAR 3). Since not all months have 31 days, when
> >> going forward or backward bewteen months with differing numbers of days, the
> >> selection does not achieve the desired result and the user is forced to tweak
> >> the date by selecting the day increment option and adjusting that to get to
> >> the correct day.
> >>
> >> Here is the current code I am using to select monthly increments, where
> >> DATEOPTION.Value = 3 is the month increment option and DATE is my date
> >> textbox:
> >>
> >> If DATEOPTION.Value = 3 Then
> >> DATE = DateSerial(Year(DATE), Month(DATE) + 1, Day(DATE))
> >> Me.DATE = CStr(FormatDateTime([DATE], [vbShortDate]) & " 23:59:00")
> >> End If
> >>
> >> I would appreciate any help in turning this into a conditional argument
> >> where if the existing date in the text box falls on the last day of the
> >> displayed month, then selecting either the forward or backward buttons would
> >> change the date to the last day of the previous or following month,
> >> regardless of the number of days in that month.
> >>
> >> Thanks in advance.
> >>
> >> L.B.
> .
>
|
Similar Threads
