Web Development

Ask something
Discussion Locked

Access Front End Question

0 helpful
9 replies

by mrdbcooper 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to get a code that determines if a
specific date is already reserved against a table
of reservations.

I have queried the table to focus in on current
and upcoming reservations. Then I have a code
written in the AfterUpdate area of the StartDate
to compare the propertyID on the form with that
in the query. That's fine.
But I cannot get it to then compare the StartDate
to see if it falls between the other Start & End
dates of the reservations for that PropertyID.

Example:
PropertyID 1 Has a reservation between 6/11/02
and 6/26/02
And has another between 7/4/02 and 7/15/02
Now I need the next reservation which starts on
6/25/02 for that property to tell me if that
is possible. Which obviously its not if you look
at the data. But I need the code to determine
this without looking at a table and MsgBox the
user that its already reserved.

For some reason it is not understanding the fact
that the new start date is between some other
start and end date.

I am probably seeing the forest and not the trees,
or vice versa here. Maybe someone can point out
a tree or two to me.

Thanks
K Darr

9 replies

by kees.valkenswaard

All the details you may have to find out yourself. Just to give an idea.
A table RESV with PropID, StartD and EndD
Make a query CHECKR with these three fields
Enter as criteria under PropID: [name of prop] (or something else)
Under StartD: [Enter Rdate]>=[StartD]
Under EndD: [Enter Rdate]<=[EndD]
Suppose:
PropID StartD EndD
Bill 4-5-2002 8-10-2002
Cas 6-2-2002 8-2-2002
Jane 11-6-2002 26-6-2002
Jane 4-7-2002 15-7-2002
Start the query CHECKR. It will ask for the name of prop, next forEnter Rdate
If the time is blocked a result will be seen, else the query will be empty.
If I enter Jane and 25-6-2002, I get the following result:
PropID StartD EndD
Jane 11-6-2002 26-6-2002
It is clear that you do not have to work with parameter criteria. This is just to show the functionality. However, a simple solution would be to connect the criteria to textboxes on a form. Running the query would show blocked dates, or nothing if the start date is free. You probably would have to check the end date as well. I lack details. In a macro it is relatively not too complicated to do the same type of tricks.
Good luck.

by kees.valkenswaard

It is obvious that StartD and EndD should be date fields. The manner in which you have to enter dates [standardized or old-fashioned mm-dd-yyyy] depends on your regional settings.

by kees.valkenswaard

If you are a pro in VBA, answer 2 gives you most of the coding required to do the job. Answer 3 may be regarded as a detailed shortcut. It is, however, far from complete.
I would use VBA myself as well in the end. I prefer to show the functionalitywith simple queries which you easily can make and test with the query builder. If they do what you want it is always possible to refine in VBA. My experience is that many people are seeing the forest and not the trees, or vice versa by starting too soon in VBA. Someone may give you a bunch of code. In that case a lot of VBA code without explanation does not help either. What I like about answer 2 is that it explains what has been done.
By the way: You will have to do a similar test for the enddate.

by mrdbcooper

Tried the idea. And both yours and the next work great on the
first record in the query, but does not then check for the next record and so on. Thanks anyway.

by shanghai sam

This might be simpler to do than answer 1:

Assume a reservations table ("RES") containing ID, StartDate, EndDate.

On your form are controls txtID, txtRequestDate

For the AfterUpdate property of the appropriate control, put in code similar to this:

Dim intCount as Integer

intCount = DCount("[ID]","[RES]","[ID] = " & me.txtID & " And [StartDate] <= #" & me.txtRequestDate & "# And [EndDate] >= #" & me.txtRequestDate & "#")

If intCount > 0 then
msgbox "Already reserved"
end if

Basically, if that (property) ID has a reservation between the two dates, intCount will be greater than 0, since dCount will count the number of records returned.

It's important to surround txtRequestDate with # signs, since it's a date. I'm assuming that your ID is a number. If it's a text then surround me.txtID with single quotes (') just like the date values are surrounded with # signs.

by itpreneur

this is how you do it.

USE "BETWEEN" FUNCTION

if inputdate BETWEEN startdate and enddate then
msgbox "it has already been reserved"
end if

by mrdbcooper

The between function is not correct in this instance. I know, I tried as that seemed the most logical conclusion. It was my first idea, but that statement is not going to work. Thanks

This discussion is closed to new replies.