Validation rule for dates

Question

Question.  I have a table for applicants, then a table for applications that come in for that applicant.  How would I set a validation for the date of application to say this:

If the date of application is within the same application month as an application that has already been received, Then a message box "There is already an application for this month."

For example, I receive an application 1/8/12 and then later one comes in for 1/26/12.  The second application cannot be entered into the system because the applications need to be combined.

Answer 1

You cannot do that by means of a Validation Rule, but you can do so in the BeforeUpdate event procedure of a form for entering data into the Applications table, e.g.

Const MESSAGETEXT = "There is already an application for this month."

Dim strCriteria As String

strCriteria = "YEAR([ApplicationDate]) = " & YEAR(Me.[ApplicationDate] & _

    " AND MONTH([ApplicationDate]) = " & MONTH(Me.[ApplicationDate]) & _

    " AND [ApplicantID] = " & Me.[ApplicantID] & _

    " AND [ApplicationID] <> " & Me.[ApplicationID]

If Not IsNull(DLookup("ApplicationID", "Applications", strCriteria)) Then

    MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"

    Cancel = True

End if

This will only provide validation when entering data via a form, however, which you might fell is adequate.  It is still possible for invalid data to be entered in some other way.  To protect against that you'd need to apply a CHECK CONSTRAINT to the table, which can be done in code, e.g. from the debug window, by entering the following, as a single line:

CurrentProject.Connection.Execute "ALTER TABLE Applications ADD CONSTRAINT OnePerMonth CHECK((SELECT COUNT(*) FROM Applications GROUP BY ApplicantID, YEAR(ApplicationDate), MONTH(ApplicationDate) HAVING COUNT(*) > 1) IS NULL)"

If you want too subsequently remove the constraint enter:

CurrentProject.Connection.Execute "ALTER TABLE Applications DROP CONSTRAINT OnePerMonth"

NB:  Name of constraint, OnePerMonth in this example, must be distinct within the database.

Answer 2

You cannot do that in the table, but you can do it in the form used to enter/edit the applications, for example in the Before Update event of the text box bound to the application date field.

Let's say the table is tblApplications with an AutoNumber primary key ApplicationID, plus a foreign key ApplicantID and a date/time field ApplicationDate.

Private Sub ApplicationDate_BeforeUpdate(Cancel As Integer)

    If DCount("*", "tblApplications", "ApplicationID<>" & Me.ApplicationID & _

            " AND ApplicantID=" & Me.ApplicantID & " AND Year(ApplicationDate)=" & _

            Year(ApplicationDate) & " AND Month(ApplicationDate)=" & Month(ApplicationDate)) > 0 Then

        MsgBox "There is already an application for this applicant this month!", vbExclamation

        Cancel = True

    End If

End Sub

Answer 3

I wouldn't use a validation rule for this as it is too complex. Use either the Before or After Update event of the control where you enter a data with code like:

If Not IsNull(DLookup("[AppDate]","tblApplications","[ApplicantID] = " & Me.ApplicantID & " AND Format([AppDate],"mmyy") = '" & Format(Me.AppDate,"mmyy") & "'") Then

MsgBox "An application already exists for this month"

End If