Query to list rooms occupied

[Scott B]

Good morning,

I have a DB for my bed & breakfast. I would like to create a query that
lists all rooms currently occupied. I have a query with a field with the
starting date (DATE/TME field) of a stay and a field with the number of
nights of the stay (LONG INTEGER number field). I have a calculated field
( "DepartDate: CVDate([StayStart]+[StayLength])" ) that tells me when the
guest is leaving. I would like to know what rooms are occupied (or vice
versa, empty) at any given time. I do not know where to start. Any help
would be appreciated. Please let me know if there is more info needed here.

Best regards,
Scott B

 

[Guest]

Assuming StayStart is the field used to enter check-in -
SELECT "RoomNumber", etc FROM tablename WHERE StayStart = date()

If using the querybuilder put Date() in the criteria for StayStart. This
will always give you records for today's date. To be more flexible, in the
critieria put [Enter Date:] (the square parens make it a prompt). This will
prompt you for a date and you can check any date you like!

 

[Scott B]

Thanks for the quick reply. I undestand what you are driving at. I tried
the SQL and it works, but I need to be clearer. I am trying to get Access
to display occupied rooms for today's date when I open a form based on this
query. I have a field for the day a guest check in and a field for the
length of stay so I calculate the departure date. Can I use any of these to
automate the occupied room process or do I need another field or fields to
accomplish this. In the end I would like to open a form or print a report
and know what rooms are occupied on the day I open the form or print the
report without input.

I hope this is a little clearer than mud.

Again, thanks,
Scott B

Assuming StayStart is the field used to enter check-in -
SELECT "RoomNumber", etc FROM tablename WHERE StayStart = date()

If using the querybuilder put Date() in the criteria for StayStart.
This
will always give you records for today's date. To be more flexible, in
the
critieria put [Enter Date:] (the square parens make it a prompt). This
will
prompt you for a date and you can check any date you like!

Good morning,

I have a DB for my bed & breakfast. I would like to create a query that
lists all rooms currently occupied. I have a query with a field with the
starting date (DATE/TME field) of a stay and a field with the number of
nights of the stay (LONG INTEGER number field). I have a calculated
field
( "DepartDate: CVDate([StayStart]+[StayLength])" ) that tells me when the
guest is leaving. I would like to know what rooms are occupied (or vice
versa, empty) at any given time. I do not know where to start. Any help
would be appreciated. Please let me know if there is more info needed
here.

Best regards,
Scott B

 

[Guest]

Try the following in the criteria
StayStart - Is Not Null
DepartDate - =>Date()

If that doesn't answer your question, will need some more information. Post
all the fields and table name(s) for the form. Is the form's recordset based

Thanks for the quick reply. I undestand what you are driving at. I tried
the SQL and it works, but I need to be clearer. I am trying to get Access
to display occupied rooms for today's date when I open a form based on this
query. I have a field for the day a guest check in and a field for the
length of stay so I calculate the departure date. Can I use any of these to
automate the occupied room process or do I need another field or fields to
accomplish this. In the end I would like to open a form or print a report
and know what rooms are occupied on the day I open the form or print the
report without input.

I hope this is a little clearer than mud.

Again, thanks,
Scott B

Assuming StayStart is the field used to enter check-in -
SELECT "RoomNumber", etc FROM tablename WHERE StayStart = date()

If using the querybuilder put Date() in the criteria for StayStart.
This
will always give you records for today's date. To be more flexible, in
the
critieria put [Enter Date:] (the square parens make it a prompt). This
will
prompt you for a date and you can check any date you like!

Good morning,

I have a DB for my bed & breakfast. I would like to create a query that
lists all rooms currently occupied. I have a query with a field with the
starting date (DATE/TME field) of a stay and a field with the number of
nights of the stay (LONG INTEGER number field). I have a calculated
field
( "DepartDate: CVDate([StayStart]+[StayLength])" ) that tells me when the
guest is leaving. I would like to know what rooms are occupied (or vice
versa, empty) at any given time. I do not know where to start. Any help
would be appreciated. Please let me know if there is more info needed
here.

Best regards,
Scott B

 

[John Spencer (MVP)]

Your might try something like the following. If it is a one-day stay, is
StayLength = 0 or 1 ? Assuming Zero, then I would try

SELECT Y.*
FROM YourTable as Y
WHERE (Y.StartDate <= Date() AND
CVDate([StayStart]+[StayLength]) >= Date())
Or Y.StartDate = Date()