Validation Rule and Now() function

[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

 

[Jeff Boyce]

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

 

[Douglas J. Steele]

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.

 

[Jeff Boyce]

Oooh, nice catch, Doug. I glossed right over that...

Jeff Boyce

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.

 

[Douglas J. Steele]

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!)

Oooh, nice catch, Doug. I glossed right over that...

Jeff Boyce

 

[Tina Hudson]

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

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

 

[Tina Hudson]

Doug,
Yes, Date() is what I used in the after update property of the date field.
Works great!