DATE SELECTION ISSUE

L

lbbeurmann

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.
 
L

lbbeurmann

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.
 
J

John Spencer

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
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 said:
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.
Click to expand...
Click to expand...
 
L

lbbeurmann

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 said:
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
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 said:
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.
Click to expand...
Click to expand...
.
Click to expand...
 
L

lbbeurmann

Your solution worked perfectly. i really appreciate the help. This has been
bugging me for days.

Thanks again,
L.B.

John Spencer said:
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
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 said:
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.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA to go to specific sheet & cell in the current workbook not working. 2
Excel Message box with days of month & date. 1
Date converting 4
Help with julian date? 2
Date Validation - First 7 days of the month 6
Find "Last Friday" function works almost 100% 10
Dates - Need to display date one month prior to user-entered date 13
DateSerial-Calculating a date 35 days from today 5

Top