Need a date comparison Validation equation

[thatlem]

I need to build a macro that will compare the audit date staff are trying to enter vs the current system date. Based on the current date - 45 days, I want to lock staff out from putting in any older data.

I have built a message to display, and want to set the validation to check before update.. something like [Audit Date] < Date()-45. if True then will display message and clear the form.

Have tried many variations on the theme, but am stumped. Suggestions.

Thanks

 

[RuralGuy]

You should be using the BeforeUpdate event of the [Audit Date] control.

 

[thatlem]

I did mention that, as will tie into the Before Update property of the [Audit Date] field - although that is not the issue, it is the syntax of the validation statement that has me confused.

 

[JANR]

You could try this in the after update event:

Private Sub Tekst7_BeforeUpdate(Cancel As Integer)
    If (Me.Tekst7 < (Date - 45)) Then
        MsgBox " not success"
        Else
        MsgBox "success"
    End If
End Sub

JR

 

[RuralGuy]

Are you getting an error with the syntax you posted? What do you have right now?

Edit: You *do* want the BeforeUpdate event of the control and then set Cancel = True to hold the focus in the control when you do not like the input.

 

[thatlem]

I have defined the following code:

Private Sub Audit_Date_BeforeUpdate(Cancel As Integer)
If (Audit_Date < (Date - 45)) Then
MsgBox "The data you are attempting to enter exceeds the 45 day limit and cannot be accepted."
DoCmd.CancelEvent
End If
End Sub

JANR, Thanks for help with the syntax. It works fine, however, I get a "Value violated the validation rule for the field or record" error that displays following the message box.

Any ideas?

 

[Dennisk]

you don't cancel the event you set the cancel variable that is an internal variable passed by Access to true

If (Audit_Date < (Date - 45)) Then
    MsgBox "The data you are attempting to enter exceeds the 45 day limit and cannot be accepted."
      Cancel = True
End If

 

[thatlem]

I made the change, but still get the validation error.

 

[RuralGuy]

Do you have a validation rule set in the table as well?

 

[thatlem]

No, do I need it in the table as well? Would it function better to define only in the table and not in the entry form - as this Audit date is spread across several audits and all tie into the same field in the main table.
Thanks

 

[RuralGuy]

No, do I need it in the table as well? Would it function better to define only in the table and not in the entry form - as this Audit date is spread across several audits and all tie into the same field in the main table.
Thanks

I prefer to *not* do any validation in a table but do it all in the form control's BeforeUpdate event. I was just trying to guess why you are still getting a validation error. Please post all of the BeforeUpdate code starting with Private Sub...

 

[thatlem]

Private Sub Audit_Date_BeforeUpdate(Cancel As Integer)
If (Audit_Date < (Date - 45)) Then
MsgBox "The data you are attempting to enter exceeds the 45 day limit and cannot be accepted."
Cancel = True
End If
End Sub

That's it....

 

[RuralGuy]

If you will use the code tags, the formatting of the code is preserved.

Private Sub Audit_Date_BeforeUpdate(Cancel As Integer)
   If (Audit_Date < (Date - 45)) Then
      MsgBox "The data you are attempting to enter exceeds the 45 day limit and cannot be accepted."
      Cancel = True
   End If
End Sub

I do not see why you are getting a validation error. What exactly does the error say and do you have a code?

 

[thatlem]

see attached jpeg. This occurs following the message box[

 

[thatlem]

I have found that this works much better by simply defining the validation fields in the main table for [Audit date], instead of either defining a macro or code. By setting the required field to yes (which I already had), and the validation expression to > date()-45 I was able to get the expression to lock out old data without the validation error appearing. Thanks for all your inputs.