Using multiple fields to validate data

[Burro]

Hello,
I am trying to validate a date field. The table contains a field for PropertyID and one for check-in and check-out dates. When the user enters a proposed check-in or out date, I want a prompt that the date is already taken if it is in the table, but only for the specific PropertyID. Since there are numerous proeprty ID's, i can't just say no duplicates allowed..
Ideally, I would like to have a validation that searches for dates BETWEEN the check-in and out dates as well, and only for the specific PropertyID.

Any help would be TREMENDOUSLY appreciated!!!

Scott Jackson

 

[JerryDennison]

I can give you a method for determining if a date is available between two dates. It's a little complicated but not severly so. I will e-mail to you if you wish. E-mail your request to chakoteh3@yahoo.com.

 

[DaveSH]

If you have a form with fields:
PropertyID
Checkindate
CheckOutdate

And a reservations table with fields
PripertyID
Checkindate
CheckOutdate

You can put the following code in the click event of a command button.

Private Sub Command8_Click()

Dim sql As String
Dim rst As Recordset

sql = "SELECT * FROM reservations WHERE "
sql = sql & "reservations.PropertyID = " & Me![PropertyID] & " AND "
sql = sql & "reservations.CheckOutdate Between #" & Me![Checkindate] & "# And #" & Me![CheckOutdate] & "#"
sql = sql & " OR reservations.Checkindate Between #" & Me![Checkindate] & "# And #" & Me![CheckOutdate] & "#"
sql = sql &amp; &quot; OR reservations.Checkindate < #&quot; &amp; Me![Checkindate] &amp; &quot;# AND &quot;
sql = sql &amp; &quot;reservations.CheckOutdate > #&quot; &amp; Me![CheckOutdate] &amp; &quot;#;&quot;

Set rst = CurrentDb.OpenRecordset(sql)

If rst.RecordCount = 0 Then
MsgBox &quot;Property Is available&quot;, vbOKOnly + vbInformation + vbDefaultButton1
Else

MsgBox &quot;Property NOT available&quot;, vbOKOnly + vbInformation + vbDefaultButton1
Me![Checkindate] = &quot;&quot;
Me![CheckOutdate] = &quot;&quot;

End If
rst.Close

End Sub

If property is available a message box will notify you of that. If not a message box will warn that it is not available, and blank the 2 date fields on the form.

I dont know what else you are doing with your database so I will just make a comment that you may want to consider a record for each day that a property is reserved. It will allow you to do alot more using simple queries. With only start and end dates you will always have to evaluate with code similar to what I have shown to make determinations.

FOr example say you wanted to print a matrix of properties and dates showing who has reserved them. If each reserved day has a record, you can create a simple crosstab query using a wizard and some minor manual alterations. To do the same thing with only start and end dates will require alot more work. Again this is just a comment and I dont know what the scope of your application is.

Hope this helps
Dave
gallagherd@earthlink.net

 

[DaveSH]

Oh BTW, your comment regarding not being able to use the NO Duplicates index is true with your current structure.

However if you had a record for each reserved day ...

Table Reservations:

ID
PropertyID
Date


you could set up a composite index using PropertyID and date with no duplicates. This would generate an error when you attempted to add a duplicate combination of PropertyID and Date. You then can trap the error and respond with VBA code to do something like display a message box or perform some other action.
Dave
gallagherd@earthlink.net

 

[jagilman]

I would avoid the use of daily entries. The check out and check in dates provide enough info for a test, however is there a need to overlap the check in and out dates. As in returning the property in the AM and checking it out in the PM.

Use the following logic for your availability test.

Just test to see if the (((checkindate) < than the proposed checkoutdate) and the ((checkoutdate) > than the proposed checkindate)).
This will let you know if it is avaliable.

To take advantage of the time element just include the same logic to the time value ( I would use 1-24 to indicate the hour of the day as an integer )
Dont store an actual time value in the database.

All you will get from this type of testing is if the Property is available or not. If you take it one step futher and populate a table or form to display the actual reservation of the property the user will be better able to take the next best step in selecting an available time period.
John A. Gilman
gms@uslink.net

 

[DaveSH]

John makes a good point about the overlap.

And I defer to his wisdom regarding the daily entries.




Dave
gallagherd1@rcn.com

 

[Burro]

Thanks for all the help guys..I am in process of implementing all.
Thanks again for taking the time to post..I can't tell you how much it relieves my stress for this project!

Scott

 

[jagilman]

Burro, I have made a quick and dirty sample database to show you how to populate a temp table with all the days affected by the users attempt to check out a property. It starts with the earlier of the checked out date or the users selected check out date and then goes to the latter date for check in. The temp table also will display each days status with respect to the checked out condition of the property.

Send me an email if you would like a copy.

Pleas note: I will be out of state as of Monday noon so don't wait if you want it this week!!!


John A. Gilman
gms@uslink.net