Re: Booking System - Vacant room report


so if we're looking at a date range of #2/15/2008# to #2/20/2008#
and room id 2 is booked on #2/17/2008# and #2/18/2008#

you want to see what, in what format ?
something like this ?

room available
2 2/15/2008 2/16/2008 2/19/2008 2/20/2008

My report currently shows the output something like

Vacant Rooms between Apr 15 and May 15 2008

Tuesday Apr 15 Room: 2, 4, 6
Wed Apr 16 Room: 1,2,7, 9

etc

I was more focussed on getting the source data, not the display
format.

Peter

Personally I'd be happy with the temp table approach, especially with a throw
away temp database.

But just for fun, if your not concerned about the display format.

The following will return a single record for the room with each vacant date
within the date range as a field in the recordset, alternately you could have it
return the the generated SQL string, which could then be set as the
recordsource of the report.

*Note: my booking system is for a modelling agency so substitute RoomID for
ModelID, your bookings table for tblJobTimes and your rooms table for tblModels.

Function fGetFreeDates(lngModelID As Long, dtStart As Date, dtEnd As Date)
Dim strSQL As String
Dim colDates As Collection
Dim rst As DAO.Recordset
Dim d1 As Date
Dim d2 As Date
Dim i As Integer

Set colDates = New Collection

'seed collection with dates
d1 = dtStart
d2 = dtEnd
Do Until d1 = d2 + 1
colDates.Add d1
d1 = d1 + 1
Loop

'get all booked dates in date range
strSQL = "SELECT RefDate FROM tblJobTimes " _
& "WHERE ((ModelID)=" & lngModelID & ") AND " _
& "((RefDate) Between #" & Format(dtStart, "mm-dd-yyyy") & "# And #" &
Format(dtEnd, "mm-dd-yyyy") & "#) " _
& "GROUP BY RefDate;"

Set rst = CurrentDb().OpenRecordset(strSQL)

'remove matching dates from collection
'leaving only free dates
If rst.RecordCount <> 0 Then
For i = colDates.Count To 1 Step -1
rst.FindFirst "((RefDate)=#" & Format(colDates(i), "mm/dd/yyyy") & "#)"
If Not rst.NoMatch Then
colDates.Remove (i)
End If
Next
End If
Set rst = Nothing

'build SQL string of free dates
If colDates.Count > 0 Then
strSQL = "SELECT ModelID, "
For i = 1 To colDates.Count
strSQL = strSQL & Chr(34) & colDates(i) & Chr(34) & " As D" & i & ", "
Next i
strSQL = Left(strSQL, Len(strSQL) - 2) & " FROM tblModels;"
'open a recordset if you want
'Set rst = CurrentDb().OpenRecordset(strSQL)
' For i = 0 To rst.Fields.Count - 1
' Debug.Print rst.Fields(i)
'Next i

'Set rst = Nothing
End If

fGetFreeDates = strSQL

End Function

Wayne Gillespie
Gosford NSW Australia
.

Relevant Pages

  • Re: Exporting data from an append table into excel without field h
    ... this is the most simple code to export to excel using copy from recordset. ... Dim objXLApp As Object 'Excel.Application ... Set rst = CurrentDb.OpenRecordset ... Set objXLWs = objXLWb.Worksheets ...
    (microsoft.public.access.externaldata)
  • Re: A command button (Access) that saves the current record in a file
    ... 'In the onclick event of command button ... Dim fso As New FileSystemObject ... Set rst = Me.frmTextFileDemoSub.Form.RecordsetClone ... 'set recordset object rst to current record ...
    (microsoft.public.access.externaldata)
  • Re: SQL Query between 2 dates
    ... dates which are being input are in the format of dd/mm/yyyy. ... Dim rs AS DAO.Recordset ... Set rst = dbEngine.OpenRecordset ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Custom ID Field
    ... field JobNumber. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... the ID field in the table is set to AutoNumber and the Format ...
    (microsoft.public.access.formscoding)
  • Re: Excel ADO Crash
    ... One other important point: the recordset ... Dim cnt As New ADODB.Connection ... Set cnt = New ADODB.Connection ... Set rst = New ADODB.Recordset ...
    (microsoft.public.excel.programming)