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..

I have a table which has all the reservations passed in (Fields being
RoomNo, ReservationName, ArrivalDate, DepartureDate etc).

My form is based on this table and after I enter the reservation name,
arrival date and departure date, on my form I have a combo box which pops up
with all the rooms.

I would like the combo box 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: angekap (AT) hol (DOT) gr

When you get down to Step 4 below, you might need a follow-up email to
finish up - this is not a simple request.

You set the source of the dropdown list itself on the Properties box,
Data tab, under Row Source. You can write SQL code right into the space
there, but I'd prefer to build a query as a saved query to produce the
list, and enter the query's name in the space instead. This project
will get a bit involved, and it will probably help to be able to review
your work easily, etc.

You need a table listing all the rooms. Hopefully you have a "Rooms"
table already. I'd expect that the RoomNo would be the ID field, with
other fields for # and type of beds, other amenities offered, etc.

You also need a text box (or other control) on the form where the
desired arrival date is entered. I won't try to automate the logic
(yet) for a multi-night stay because it gets more involved, but we will
need to cover that.

Put the Reservations and Room tables in one query. Put in a join line
from the RoomID of one to the RoomID field of the other. Right-click on
the line to get a 2-line menu, pick Join Properties, and set it to read
"Include ALL records from the 'Rooms' table and only those records from
'Reservations' where the joined fields are equal."

Now from the Rooms table, put the RoomNo field on the grid, and from
Reservations, put the RoomNo and the Date fields. If you run this query
now, you will get every room showing up at least once even if it was
never booked. If booked more than once, it would have multiple rows,
one for each reservation. Four steps will turn this query into what you
need:

1) Specify a date. For now, just enter a date manually in the Date
fields's criteria row.

2) Tweak the RoomNo fields to make more readable. Specifically, in the
Reservations RoomNo field, change the field entry this way:

[from this]
RoomNo
Reservations

[to this instead]
ResRoom:RoomNo
Reservations

so that the column will have the recognizable alias label"ResRoom".

3) In the ResRoom field, enter the criteria Is Null (use no quotes or
brackets or anything). If you just type in the word null it will make
the needed correction. This will give you rooms with no reservation for
that date. You should run it to see the results.

4) You need to modify the date field's criteria in the query to work
from the dates entered on the form, and to handle the range of dates
covered by a multi-night stay.

The query can reference data in the form. In the Criteria row,
right-click, select "Build", and use the three lower panels to NAVIGATE
through "Forms" to select and specify the text box(es) with the new
guest's date information.

If each night reserved is a row/record in the Reservations table, you
can write this in the "Build" window, where [FormStartDate] and
[FormEndDate] are to be replaced by the navigated references to the
form's two date boxes:
Between [FormStartDate] And [FormEndDate]

If each reservation is only one line, with a number to indicate how many
nights, then you can email me to get the rest of what you need. Some
details would depend on how the table records the data.

If the form is open, with valid dates entered, the query will run and
get data from it. You can build the query with the form closed, but it
needs to be open with data in order to run.

I hope you'll write back and let me know how this goes.

Bruce Pick
email = [brucepick1] [at] [comcast] [dot] [net]
former address:
Posidonos Peninde
top floor
Athaene
What a view of the water!

Ange Kappas wrote: