date validation + 3 days

[teiben]

I have 2 fields on a form datein and duedate. Users are entering a duedate which I dont want to be less that the indate. For example if today is 9/20/04, the user's should not be able to enter 9/15/04. What I'm stuck on is how to code the duedate to allow for 3 days. So if today is 9/20/04, the due date can't be 9/21/04, 9/22/04, or 9/23/04.

This is the code I have:
If DateDiff("d", Me.DueDate, Me.dateIn) >= 3 Or DueDate <= dateIn Then
MsgBox "You must allow 3 days to process all requests.", vbExclamation, "Improper Date"
Me.DueDate.Undo
Cancel = True
End If
End Sub

What am I doing wrong?

 

[ColinEssex]

Try

If DateDiff("d", Me.DateIn, Me.Duedate) <= 3 Or Me.DueDate <= Me.dateIn Then

etc


Col

 

[teiben]

It works! Thank you; But now I'm getting the message "The Value in the field of records violates the valiation rule for this record of field". Other than the posted code, there is no valiation at the table level or the field level.

 

[PierreR]

Try this, teiben. Notice that the underscore at the end of the Msgbox line splits that line in two, but it works like one line. You had it right, except for two mistakes. You had the date fields wrong way round, and a > instead of a < sign. Because you want DueDate to be at least 3 days BIGGER than the InDate, that also makes your "Or DueDate <= dateIn" unnecessary.

Private Sub DueDate_BeforeUpdate(Cancel As Integer)
    If DateDiff("d", Me.DateIn, Me.DueDate) <= 3 Then
        MsgBox "You must allow 3 days to process all requests.", _
        vbExclamation, "Improper Date"
        Me.DueDate.Undo
        Cancel = True
    End If
End Sub

 

[PierreR]

Teiben. I just noticed that your users would be able to enter the wrong date if they first fill in the DueDate and then the DateIn.

This can be prevented by placing exactly the same coding in the BeforeUpdate event of the InDate as well. The module will then look like this:

Private Sub DateIn_BeforeUpdate(Cancel As Integer)
If DateDiff("d", Me.DateIn, Me.DueDate) <= 3 Then
        MsgBox "You must allow 3 days to process all requests.", _
        vbExclamation, "Improper Date"
        Me.DueDate.Undo
        Cancel = True
    End If
End Sub

Private Sub DueDate_BeforeUpdate(Cancel As Integer)
    If DateDiff("d", Me.DateIn, Me.DueDate) <= 3 Then
        MsgBox "You must allow 3 days to process all requests.", _
        vbExclamation, "Improper Date"
        Me.DueDate.Undo
        Cancel = True
    End If
End Sub

 

[teiben]

Thanks you. (I put the field datein and locked it, so the users can't change it.) I'm using the code you posted and now I'm not getting that stupid "the value in the field of records violates the validation rule for this record of field".

 

[teiben]

Anyway to specify business days only (ignore weekends)?

 

[teiben]

HELP! I have the code in place and the record will save with the duedate less than the date in. The messag works, but that's all

 

[PierreR]

I include this very small download of the example I worked out. Tell me if it acts up the same way. It might help hone in on the problem.

 

[teiben]

Thanks. I forgot to mention I'm using 97