Dates - preventing overlap

[Guest]

I'm working on a form where the users are entering effective periods for
membership (usually calendar year, but it doesn't have to be). In the past,
users have been known to enter duplicate or overlapping contract periods.
Here's the setup:

Table1
AccountNo BeginDate EndDate
1 1/1/06 12/31/06
1 1/1/07 12/31/07
2 6/1/05 5/31/06

They are using a form to enter to contract periods, but I need to validate
the dates they enter and make sure the account is not 'active' for any dates
they enter on the form. For instance, I need to prevent them from entering a
contract period of 3/1/06 - 6/30/06 for Account # 1. To throw another twist
in, the contract period can be longer than 1 year.

Any suggestions?

Thanks in advance.
J

 

[Allen Browne]

Two events overlap if:
- A starts before B ends, and also
- B starts before A ends.

This kind of thing:

Private Sub Form_BeforeUpate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If Not (IsNull(Me.BeginDate) OR IsNull(Me.EndDate) OR
IsNull(Me.AccountNo)) Then
strWhere ="(BeginDate < " & Format(Me.EndDate], "\#mm\/dd\/yyyy\#")
& _
") AND (" & Format(Me.StartDate], "\#mm\/dd\/yyyy\#") & _
" < EndDate) AND (AccountNo = " & Me.AccountNo & ")"
varResult = DLookup("AccountNo", "Table1", strWhere)
If Not IsNull(varResult) Then
If MsgBox("Overlap! Continue anyway?", vbYesNo+vbDefaultButton2)
= vbNo Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

 

[kingston via AccessMonster.com]

I don't think that this can be done with simple field validation criteria.
Try the following (AccountNo must be filled out first):

string = "SELECT TOP 1 [EndDate] FROM [Table1] WHERE ([AccountNo]=" & Me.
AccountNoControl & ") ORDER BY [EndDate] DESC;"
recordset.Open string

maxEndDate = recordset!EndDate

If me.BeginDate > maxEndDate then
'good
else
MsgBox "Account still active. Begin date must be after " & maxEndDate
me.BeginDate = maxEndDate + 1 'or whatever
End If

HTH.