Make sure end date is one year greater than start date

[AimeeK]

Hello, I am trying to create a form in which the user is required to enter a
contract start date and a contract end date. An example of this is:

[CONTRACT START DATE] 10/01/2005...Textbox control name is Text7
[CONTRACT END DATE] 09/30/2006...Textbox control name is Text5

How can I make sure that the user doesn't accidentally enter 09/30/2005?
Please let me know which control to put the code in and which event to put it
under. I'd prefer to use an If...Then statement to the tune of something
like this:

If Me.Text5<Me.Text7 Then
msg = MsgBox("CONTRACT END DATE must be greater than the CONTRACT START
DATE", vbCritical) = vbOk
Me.Text5.SetFocus
End If

Any and all suggestions are most appreciated! Thanks in advance for your
help.

 

[Douglas J. Steele]

Since the user may type the dates in reverse order (i.e.: type the End
before they type the Start), your best bet is probably to create a routine
that you can call in the AfterUpdate event of both text boxes. Something
like:

Sub CheckDates()

If Len(Me.Text7 & "") > 0 _
And Len(Me.Text5 & "") > 0 Then
If CDate(Me.Text5) < CDate(Me.Text7) Then
MsgBox "The End Date must be after the Start Date", _
vbOkOnly + vbError
Me.Text5.SetFocus
End If
End If

End Sub

 

[John Vinson]

Hello, I am trying to create a form in which the user is required to enter a
contract start date and a contract end date. An example of this is:

[CONTRACT START DATE] 10/01/2005...Textbox control name is Text7
[CONTRACT END DATE] 09/30/2006...Textbox control name is Text5

How can I make sure that the user doesn't accidentally enter 09/30/2005?
Please let me know which control to put the code in and which event to put it
under. I'd prefer to use an If...Then statement to the tune of something
like this:

If Me.Text5<Me.Text7 Then
msg = MsgBox("CONTRACT END DATE must be greater than the CONTRACT START
DATE", vbCritical) = vbOk
Me.Text5.SetFocus
End If

Rather than letting the user type in a wrong date, and slapping their
hand if they fail, consider two options:

- If the Contract End Date is always a year after the start date,
don't store it in your table at all; instead, just set the Control
Source of Text5 (for goodness' sake, though, rename the controls to
meaningful names!!) to

=DateAdd("yyyy", 1, [Contract Start Date])

- If that would be the default date but should allow the user to
override it, put code in Text7's AfterUpdate event;

Private Sub Text7_AfterUpdate()
If IsNull(Me!Text5) Then ' don't stomp on existing data
Me!Text5 = DateAdd("yyyy", 1, Me!Text5)
End if
End Sub

John W. Vinson[MVP]

 

[AimeeK via AccessMonster.com]

Douglas, Thank you for your reply. The code now does everything I need it to,
EXCEPT set the focus back to Text5...I put this code in the AfterUpate()
events for both text controls like you said, and I did make a couple of
changes, but nothing major...this is what I did:

' Sub CheckDates() '

If Len(Me.Text7 & "") > 0 And Len(Me.Text5 & "") > 0 Then
If CDate(Me.Text5) < CDate(Me.Text7) Then
msg = MsgBox("The End Date must be after the Start Date",
vbCritical) = vbOK
Me.Text5.SetFocus
End If
End If

I need the user to hit 'OK' when the message box comes up, and for the Focus
to be set back to Text5...I also need to the focus to 'stay' in that text
control until a valid date is typed. This is the first major database I have
developed on my own, hence the non-meaningful control names. By the time I
thought of changing them, I had already gotten pretty involved and at this
point, it's too late to go back and change all the names.

Thanks again.

Since the user may type the dates in reverse order (i.e.: type the End
before they type the Start), your best bet is probably to create a routine
that you can call in the AfterUpdate event of both text boxes. Something
like:

Sub CheckDates()

If Len(Me.Text7 & "") > 0 _
And Len(Me.Text5 & "") > 0 Then
If CDate(Me.Text5) < CDate(Me.Text7) Then
MsgBox "The End Date must be after the Start Date", _
vbOkOnly + vbError
Me.Text5.SetFocus
End If
End If

End Sub

Hello, I am trying to create a form in which the user is required to enter
a

[quoted text clipped - 17 lines]

Any and all suggestions are most appreciated! Thanks in advance for your
help.

 

[AimeeK via AccessMonster.com]

John, Your code works wonderfully!!! However, I do need the contract end
date format to be MM/DD/YYYY (I do have an input mask inplace, but it didn't
get picked up), and the end date to be the day before the start date, but
with next year's date...e.g., start date is 10/01/2005, and therefore end
date should be 09/30/2006...know what I mean??? I'm guessing this only
requires minor changes to your code.

And FYI, this is the first major DB I have developed on my own, so I am very
much a novice when it comes to all this, and I didn't think to rename all my
controls. However, I think I may so involved in this that may be too late to
rename everything...sorry and thanks again.

Hello, I am trying to create a form in which the user is required to enter a
contract start date and a contract end date. An example of this is:

[quoted text clipped - 12 lines]

Me.Text5.SetFocus
End If

Rather than letting the user type in a wrong date, and slapping their
hand if they fail, consider two options:

- If the Contract End Date is always a year after the start date,
don't store it in your table at all; instead, just set the Control
Source of Text5 (for goodness' sake, though, rename the controls to
meaningful names!!) to

=DateAdd("yyyy", 1, [Contract Start Date])

- If that would be the default date but should allow the user to
override it, put code in Text7's AfterUpdate event;

Private Sub Text7_AfterUpdate()
If IsNull(Me!Text5) Then ' don't stomp on existing data
Me!Text5 = DateAdd("yyyy", 1, Me!Text5)
End if
End Sub

John W. Vinson[MVP]

 

[John Vinson]

John, Your code works wonderfully!!! However, I do need the contract end
date format to be MM/DD/YYYY (I do have an input mask inplace, but it didn't
get picked up),

Just set the Format property of the textbox to mm/dd/yyyy.

and the end date to be the day before the start date, but
with next year's date...e.g., start date is 10/01/2005, and therefore end
date should be 09/30/2006...know what I mean??? I'm guessing this only
requires minor changes to your code.

DateAdd("d", -1, DateAdd("yyyy", 1, [StartDate]))

will do it. If you want to handle weekends or holidays it gets more
complicated but can still be done.

John W. Vinson[MVP]

 

[AimeeK via AccessMonster.com]

It worked! Thank you again!

John, Your code works wonderfully!!! However, I do need the contract end
date format to be MM/DD/YYYY (I do have an input mask inplace, but it didn't
get picked up),

Just set the Format property of the textbox to mm/dd/yyyy.

and the end date to be the day before the start date, but
with next year's date...e.g., start date is 10/01/2005, and therefore end
date should be 09/30/2006...know what I mean??? I'm guessing this only
requires minor changes to your code.

DateAdd("d", -1, DateAdd("yyyy", 1, [StartDate]))

will do it. If you want to handle weekends or holidays it gets more
complicated but can still be done.

John W. Vinson[MVP]

 

[AimeeK via AccessMonster.com]

Actually, one last question...how do I account for a leap year? For instance,
if a contract were to start on 02/29/2008, then I would need it to end on
02/28/2009. In testing this, the contract end date is showing as 02/27/2009.
Just another one of those "what if" scenarios I know my boss will come up
with...thanks again.

John, Your code works wonderfully!!! However, I do need the contract end
date format to be MM/DD/YYYY (I do have an input mask inplace, but it didn't
get picked up),

Just set the Format property of the textbox to mm/dd/yyyy.

and the end date to be the day before the start date, but
with next year's date...e.g., start date is 10/01/2005, and therefore end
date should be 09/30/2006...know what I mean??? I'm guessing this only
requires minor changes to your code.

DateAdd("d", -1, DateAdd("yyyy", 1, [StartDate]))

will do it. If you want to handle weekends or holidays it gets more
complicated but can still be done.

John W. Vinson[MVP]

 

[John Vinson]

Actually, one last question...how do I account for a leap year? For instance,
if a contract were to start on 02/29/2008, then I would need it to end on
02/28/2009. In testing this, the contract end date is showing as 02/27/2009.
Just another one of those "what if" scenarios I know my boss will come up
with...thanks again.

DateAdd() is clever enough to figure that out. Try it.

John W. Vinson[MVP]