Validation Rule and Now() function

  • Thread starter Thread starter Tina Hudson
  • Start date Start date
T

Tina Hudson

Hello!

I have code in the change property for the field "StaffDate" that checks to
see if the date entered is later than today. The following code doesn't
work, as if I enter today's date, I get the error message. Any help will be
appreciated.

Here is my code:

Private Sub StaffDate_Change()
With Me
If Me.StaffDate > Now() Then
MsgBox "You entered a date in the future. The date of the staffing
can not be later than today's date. Press OK to continue.", vbOKOnly,
"Invalid Staffing Date"
DoCmd.GoToControl "StaffDate"
ElseIf Me.StaffDate < #1/1/2007# Then
MsgBox "You entered an incorrect date. The date of the staffing may
not be earlier than January 1, 2007. Press OK to continue.", vbOKOnly,
"Invalid Staffing Date"
DoCmd.GoToControl "StaffDate"

End If
End With

End Sub
 
Tina

You are comparing a date value to Now(). Now() returns a date AND time
value. A date-only value has .00000 for the time-portion, so naturally,
Now() would be greater than a date-only value.

Compare to Date() instead...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
One problem is that you're using the wrong event.

The Change event fires every time the content changes: in other words, it
fires one for each key stroke.

Try putting your code into the text box's AfterUpdate event, which only
fires once the user hits Enter or moves to another field.
 
Oooh, nice catch, Doug. I glossed right over that...

Jeff Boyce

Douglas J. Steele said:
One problem is that you're using the wrong event.

The Change event fires every time the content changes: in other words, it
fires one for each key stroke.

Try putting your code into the text box's AfterUpdate event, which only
fires once the user hits Enter or moves to another field.
Click to expand...
 
I hope that that's the correct answer. When I did a test (granted, on an
unbound text box), referring to the box resulted in Null, as opposed to
anything useful. Null > Now() should not be true. However, your suggestion
(to use Date rather than Now) didn't make sense to me, since she was
checking for > Now(), not < Now().

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Boyce said:
Oooh, nice catch, Doug. I glossed right over that...

Jeff Boyce
Click to expand...
 
Eureka! It works great! : )

I wasn't sure which property to put the code on, so I appreciate your
clearing that up for me as well.

Have a great day and thanks a million,
Tina Hudson


Jeff Boyce said:
Tina

You are comparing a date value to Now(). Now() returns a date AND time
value. A date-only value has .00000 for the time-portion, so naturally,
Now() would be greater than a date-only value.

Compare to Date() instead...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Doug,
Yes, Date() is what I used in the after update property of the date field.
Works great!
 
Back
Top