A
Ange Kappas
I am trying to make a workable database with Access for a reservations
program for a small hotel that I own BUT I am stuck at either a SQL command
or Visual Basic code which will tell me which rooms are free when I am
trying to make a reservation.
Let me be more specific..
The Table which stores the reservations made has the following approximate
setup( I am including the relative fields)
RESNAME text
ROOMNO text (***Which is a lookup field from the table which has the
all the rooms Table Name is ROOMS)
ARRIVAL date
DEPARTURE date
The Table name is RESERVATIONS
The Form which stores the data for the RESERVATIONS is named RESERVATIONS2
Every time I enter data through the Form RESERVATIONS2 it stores in the
table as follows:
ROOMNO RESNAME ARRIVAL DEPARTURE
102 Smith Mr & Mrs 2-5-04
9-5-04
103 Jones Mr & Mrs 2-5-04
9-5-04
203 Hampden Mr & Mrs 5-5-04 11-5-04
e.t.c
Also the main point being is that as I enter a new reservation on my form I
enter the data for reservation name (RESNAME) then I proceed to Arrival
Date(ARRIVAL) then Departure Date (DEPARTURE) and then go to Room Number
which is the combo box (it looks up the table containing all the rooms of
the hotel).
What the criteria has to do is to lookup either a new query made which
returns all available rooms ACCORDING to the dates entered beforhand
concerning arrival and departure or use a SELECT command. Do take into
consideration that the dates entered on the form are the basis to see what
rooms are available.
What I have tried to do is create a query based on the reservation table and
inner join the rooms table but I can't seem to write the right criteria to
filter out all the available rooms.
Notice the rows corresponding to each room which are null. Say for example I
have reservations for all rooms that does not necessarily mean that the
hotel is full because the arrival and departure dates are limited. So if I
wrote a criteria for null values it would not be correct.
This is a bit more complex that what it seems since it has to deal with date
ranges.
I would like the combo box in my Form to show ONLY the rooms which are
available and
that is after I have entered arrival and departure dates on my form.
Now I don't know if a query show be used or some kind of SQL command on the
Row Source property or even a Visual Basic Code.
If anyone can help it would be much appreciated.
E Mail address: (e-mail address removed)
program for a small hotel that I own BUT I am stuck at either a SQL command
or Visual Basic code which will tell me which rooms are free when I am
trying to make a reservation.
Let me be more specific..
The Table which stores the reservations made has the following approximate
setup( I am including the relative fields)
RESNAME text
ROOMNO text (***Which is a lookup field from the table which has the
all the rooms Table Name is ROOMS)
ARRIVAL date
DEPARTURE date
The Table name is RESERVATIONS
The Form which stores the data for the RESERVATIONS is named RESERVATIONS2
Every time I enter data through the Form RESERVATIONS2 it stores in the
table as follows:
ROOMNO RESNAME ARRIVAL DEPARTURE
102 Smith Mr & Mrs 2-5-04
9-5-04
103 Jones Mr & Mrs 2-5-04
9-5-04
203 Hampden Mr & Mrs 5-5-04 11-5-04
e.t.c
Also the main point being is that as I enter a new reservation on my form I
enter the data for reservation name (RESNAME) then I proceed to Arrival
Date(ARRIVAL) then Departure Date (DEPARTURE) and then go to Room Number
which is the combo box (it looks up the table containing all the rooms of
the hotel).
What the criteria has to do is to lookup either a new query made which
returns all available rooms ACCORDING to the dates entered beforhand
concerning arrival and departure or use a SELECT command. Do take into
consideration that the dates entered on the form are the basis to see what
rooms are available.
What I have tried to do is create a query based on the reservation table and
inner join the rooms table but I can't seem to write the right criteria to
filter out all the available rooms.
Notice the rows corresponding to each room which are null. Say for example I
have reservations for all rooms that does not necessarily mean that the
hotel is full because the arrival and departure dates are limited. So if I
wrote a criteria for null values it would not be correct.
This is a bit more complex that what it seems since it has to deal with date
ranges.
I would like the combo box in my Form to show ONLY the rooms which are
available and
that is after I have entered arrival and departure dates on my form.
Now I don't know if a query show be used or some kind of SQL command on the
Row Source property or even a Visual Basic Code.
If anyone can help it would be much appreciated.
E Mail address: (e-mail address removed)