Validate overlapping time windows

[gavine]

I have a form validation challenge for which I humbly request your support.

I am building a database for phone bill management. Phone numbers are assigned to users for a period of time, and then the same number is assigned to a different user for another period of time. Users should be billed for their usage only for calls made while the number is assigned to them.

I have a table called "[Phone Assignment List]" which captures [PHONENUMBER], [Full Name], [Date Issued], and [Date Returned].

[Date Issued] is a required field, but [Date Returned] is not.

I want to add a validation rule on the [Date Issued] field such that when I populate the field, the rule evaluates that the date value for this field, associated with this phone number, does not fall within the [Date Issued] and [Date Returned] time fence for any other records with this phone number (otherwise there would be a period of time when two users could have the same phone number, which is not allowed).

Can you help?

Additionally I would like a report which would identify any ownership time gaps for any phone numbers, but this is kind of a separate challenge.

Thank you!

 

[Beetle]

You would do this in the Before Update event of your form. The code might look something like;

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg As String
    
    strMsg = "Invalid entry. This phone is currently assigned, or was" & vbCrLf
    strMsg = strMsg & "assigned to another user on the date that you entered."

    If Nz(DLookup("PhoneNumber", "PhoneAssignmentList", "PhoneNumber='" & Me!PhoneNumber _
              & "' And [Date Issued] <=#" & Me![Date Issued] _
              & "# And ([Date Returned] Is Null Or [Date Returned] >=#" _
              & Me![Date Issued] & "#)"), 0) > 0 Then
              MsgBox strMsg
              Cancel = True
              Me.DateIssued.SetFocus
    End If
    
          
End Sub

 

[gavine]

Many thanks for the quick and useful reply! I'm confident that this is the right direction, but I can't get the code to work yet. I made a few small field name corrections below to match my field and table names. However when I run this I get a "Run Time Error 3464: Data Type Mismatch in criteria expressions" error.

The debugger highlights the code beginning with "If Nz..." and ending with "...> 0 Then"

The Date Issued and Date Returned fields are both Date/Time data type.
The PHONENUMBER field is Number data type with field size Double.

Any ideas?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Invalid entry. This phone is currently assigned, or was" & vbCrLf
strMsg = strMsg & "assigned to another user on the date that you entered."
If Nz(DLookup("PhoneNumber", "[Phone Assignment List]", "PhoneNumber='" & Me!PhoneNumber _
& "' And [Date Issued] <=#" & Me![Date Issued] _
& "# And ([Date Returned] Is Null Or [Date Returned] >=#" _
& Me![Date Issued] & "#)"), 0) > 0 Then
MsgBox strMsg
Cancel = True
Me.[Date Issued].SetFocus
End If
End Sub



Thanks again for your help!

 

[Beetle]

The PHONENUMBER field is Number data type with field size Double.

Odd data type for a phone number field (normally this would be text), but if that's the case then you need to remove the single quotes surrounding the PhoneNumber reference in the criteria. I have highlighted them in red below;

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

strMsg = "Invalid entry. This phone is currently assigned, or was" & vbCrLf
strMsg = strMsg & "assigned to another user on the date that you entered."

If Nz(DLookup("PhoneNumber", "[Phone Assignment List]", "PhoneNumber=[COLOR="Red"]'[/COLOR]" & Me!PhoneNumber _
& "[COLOR="red"]'[/COLOR] And [Date Issued] <=#" & Me![Date Issued] _
& "# And ([Date Returned] Is Null Or [Date Returned] >=#" _
& Me![Date Issued] & "#)"), 0) > 0 Then
    MsgBox strMsg
    Cancel = True
    Me.[Date Issued].SetFocus
End If

End Sub

BTW - When you post code, it makes it easier to read if you wrap it in the code tags (the hash (#) tool button).

 

[gavine]

Thanks again for the instructive response.

With the change you recommended, the code works such that I see the "Invalid entry" message box when I make a change. However this box triggers even when the update should be accepted, so I think the logic is not yet right.

I'm trying to learn to read the code, and I'm making the following assumptions. Please correct me if they are untrue:
[Date Issued] evaluates the values in this column on the table.
Me![Date Issued] evaluates the value in this field on the form.
The logic in the code your provided will trigger the message box (the Invalid Entry error) if it returns TRUE.

If this is the correct syntax, then I think the logic I need might be as simple as this:

Nz([Date Returned],"12/12/9999") >= Me![Date Issued]

I believe the above would return TRUE (thus triggering the error message) IF the Date Issued value I enter in the form is less than (earlier than) the Date Returned on any other record for that phone number, OR IF the Date Returned on any other phone number is null (thus represented as 12/12/9999, which is greater than any date I will put into Date Returned on the form).

Do you think this logic makes sense? If so, can you help me format it properly into your code? I attempted through guess and check but was not successful. Also, I am concerned that this logic will not work if the record being entered on the form has a null Return Date. Is there a way to exclude the current record Return Date from the evaluation?

Once again, I appreciate your support and am open to expertise if you think there is a better way of doing this. Thanks again!

 

[Beetle]

This was just sort of air code, so it probably does need some tweaks.

[Date Issued] evaluates the values in this column on the table.
Me![Date Issued] evaluates the value in this field on the form.

That's correct

The logic in the code your provided will trigger the message box (the Invalid Entry error) if it returns TRUE.

The DLookup either returns the field value, if it finds a match, or it returns Null. The expression in my example uses the Nz function to convert Null to 0 and then determine if a 0 was returned or not. Now that I look at it, there is a bit of a typo there. The comparison (right before the Then) should be <> 0 instead of > 0. Another option would be to simply check for Null instead of using the Nz function. Example;

If Not IsNull(DLookup("PhoneNumber", "[Phone Assignment List]", "PhoneNumber='" & Me!PhoneNumber _
& "' And [Date Issued] <=#" & Me![Date Issued] _
& "# And ([Date Returned] Is Null Or [Date Returned] >=#" _
& Me![Date Issued] & "#)"), 0) Then

As far as the logic goes, I assumed the following;

1) If the DateReturned for any given record is Null, then the phone is still assigned to someone and should not be assigned to any other person.

2) If the phone has been returned (DateReturned is not Null), then for any new record the DateIssued must be greater than the DateReturned for any other existing record for that phone.

3) I also assumed that the DateIssued for any previous record would, presumably, be prior to any value that was entered for DateIssued on a new record. If you happened to enter a DateIssued value on a new record that was prior to the DateIssued values of any previous records, then the expression would fail because it doesn't compare that scenario.0

If there are other logic issues that need to be considered you'll have to let me know what those are.

However, now that I think about it, this expression may not work as is because it might cause you problems when trying to assign a DateReturned for an existing record. Your idea of using Nz(DateReturned, '12/12/9999') probably won't solve the issue. Is there a PK field in your table, or some field that uniquely identifies each row? If so we could add that into the logic to solve that problem.

 

[gavine]

Thanks again for the response.

I agree with the logical assupmtions you specified. These statements are all true.

I applied your Not IsNull code below, however, the code is still not working as expected. The Invalid Entry message is firing when I try to change the Date Issued of an existing record, and when I try to add a Date Returned to an existing record.

Note: I made two corrections to your code. First I removed the extra apostophies which you identified were not needed in your first response. Second I removed the ", 0" at the end of the IsNull() statement, which I believe was leftover from the original Nz() statement.

I like your idea of adding the record PK to the logic, so that the current record return date is not considered when evaluating the current record issue date. The table has a autonumber PK counting up from 1. Can you help me figure out how to add this component and resolve the above issues?

Thanks again for the wonderful support!

 

[Beetle]

So here is an example with a PK field added. Keep in mind that for this example I have assumed a numeric data type for the PKField. If it's a text data type you'll need to add string delimiters (The single quotes like I had around the PhoneNumber field in the first example);

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg As String
    
    strMsg = "Invalid entry. This phone is currently assigned, or was" & vbCrLf
    strMsg = strMsg & "assigned to another user on the date that you entered."

    If Not IsNull(DLookup("PhoneNumber", "PhoneAssignmentList", "PKField <> " & Me![PK Field] _
              & " And PhoneNumber = " & Me!PhoneNumber _
              & " And [Date Issued] <=#" & Me![Date Issued] _
              & "# And ([Date Returned] Is Null Or [Date Returned] >=#" _
              & Me![Date Issued] & "#") Then
              MsgBox strMsg
              Cancel = True
              Me.DateIssued.SetFocus
    End If
    
          
End Sub

You also may need to play around with the less than or equal to par as well. In other words, if a phone can be re-assigned on the same day that it was returned, then you probably want;

& "# And ([Date Returned] Is Null Or [Date Returned] >#" _

(equal (=) operator removed)

Same thing may go for DateIssued also. If it's possible that a phone could be issued, returned, then re-issued all on the same day, then you would want to remove the = from that part of the criteria as well.

Trial and error, trial and error, eventually it will work correctly.