Cant figure this out

[SFCSCOTT]

I am trying to build a database that is used for a HOTEL. I have a
table that has two fields DateIn and DateOut. How do I get the days
in between to show up in a report. For example if a guest checks in
on 1 Oct 07 and Checks Out on 9 Oct 07. How do I get it to show
1,2,3,4,5,6,7,8 Oct 07 in my query. I have built queires for 31
days. Thanks for the help

2LT Heeren

 

[Allen Browne]

The dates have to come from somewhere, you will need a table of dates.

Create a table with just one field of type Date/Time, named (say) TheDate.
Mark it as primary key. Save the table as tblDate. Type in all the dates you
need to cover, or use the function below to populate it for you.

Now create a query that uses your booking table as well as tblDate. There
must be no line joining the 2 tables in the upper pane of table design. This
gives every combination. Now drag tblDate.TheDate into the Field, row. In
the Criteria row under this field, enter:
Between [DateIn] And [DateOut]
This gives you a record for every date in the range.
Save the query, and use it as the source for your report.

Here's the function to populate the table without having to type in 10 years
worth of dates:

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function