Time Sensitive Calculation Question

[Guest]

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is that I am putting a parameter of Between two dates in the
checkin field.

Nights: IIf(DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is
Null,Date(),[CheckOutDate]))=0,1,DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is Null,Date(),[CheckOutDate])))

My challenge is if I input Between 09/01/2006 and 09/30/2006. How can I
only count the days in September and not those dates that may have happend
prior for example if people checked in on August 28 and Check out on
September 3. I would only want the query to count the two nights in
September. Also if they
checked in on September 29 and Checked Out on October 4th I would only want
the days in September. Any Ideas.

Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006

 

[Allen Browne]

Rose, the simplest way to do this will be to create a table of dates, and
then use it in a query to give you a record for each night of the booking.
You can then turn it into a Totals query to group by the month, and give the
count of nights booked for each month.

1. Create a table with one date/time field named (say) TheDate.
Mark the field as primary key.
Save the table as tblDate.
Enter records for all the dates you could need.
(Use the function below to enter dates programmatically if you wish.

2. Create a query using both tblDate and your existing table (called Table1
in the example below.)
There must be no line joining the tables in the upper pane of query design.
This is called a Cartesian Product, and gives you every combination.
Drag tblDate.TheDate into the grid, and enter this criteria under it:
Between Table1.CheckInDate And Nz(Table1.CheckOutDate, Date())
This gives you a record for each day in the range.

3. Depress the Total icon on the toolbar (upper sigma icon).
Access adds a Total row to the grid.
In the Total row under tblDate.TheDate, choose Where.
If you want to limit results to a particular month, add tblDate.TheDate
again, choose Where, and enter your limiting dates as criteria.

4. Enter this expression into a fresh column in the Field row:
TheYear: Year(tblDate.TheDate)
Accept Group By in the Total row.

5. Enter this expression into a fresh column in the Field row:
TheMonth: Month(tblDate.TheDate)
Accept Group By in the Total row.

6. Drag the primary key from Table1 into the grid.
In the Total row, under this field choose Count.

The query output now shows the number of nights booked for each month.

Here's the function that can populate the dates for you.
To add all dates between 1/1/2007 and 12/31/2010:
a) Press Ctrl+G to open the Immediate window.
b) Enter:
? MakeDates(#1/1/2007#, #12/31/2010#)


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


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is that I am putting a parameter of Between two dates in the
checkin field.

Nights: IIf(DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is
Null,Date(),[CheckOutDate]))=0,1,DateDiff("d",[CheckInDate],IIf([CheckOutDate]
Is Null,Date(),[CheckOutDate])))

My challenge is if I input Between 09/01/2006 and 09/30/2006. How can I
only count the days in September and not those dates that may have happend
prior for example if people checked in on August 28 and Check out on
September 3. I would only want the query to count the two nights in
September. Also if they
checked in on September 29 and Checked Out on October 4th I would only
want
the days in September. Any Ideas.

Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006

 

[Jamie Collins]

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is that I am putting a parameter of Between two dates in the
checkin field.

Nights: IIf(DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is
Null,Date(),[CheckOutDate]))=0,1,DateDiff("d",[CheckInDate],IIf([CheckOutDa­te] Is Null,Date(),[CheckOutDate])))

My challenge is if I input Between 09/01/2006 and 09/30/2006. How can I
only count the days in September and not those dates that may have happend
prior for example if people checked in on August 28 and Check out on
September 3. I would only want the query to count the two nights in
September. Also if they
checked in on September 29 and Checked Out on October 4th I would only want
the days in September. Any Ideas.

Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006

I'd go with Allen's proposal, being a standard SQL trick known as a
Calendar table e.g. (see http://www.aspfaq.com/show.asp?id=2519).

For a more direct answer to your question, see the VBA below code that
creates a test database, table, data, procedure (Parameter Query) and
executes it to return the required (I think) resultset:

Sub Rose()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & vbCr & "CheckInDate" & _
" DATETIME NOT NULL, " & vbCr & "CheckOutDate" & _
" DATETIME, " & vbCr & "CHECK(CheckInDate" & _
" < CheckOutDate)" & vbCr & ")" & vbCr & ";"
.Execute _
"INSERT INTO Test (CheckInDate," & _
" CheckOutDate) " & vbCr & "VALUES (#2006-09-29" & _
" 00:00:00#, #2006-10-04 00:00:00#);"
.Execute _
"INSERT INTO Test (CheckInDate," & _
" CheckOutDate) " & vbCr & "VALUES (#2006-09-01" & _
" 00:00:00#, #2006-09-05 00:00:00#);"
.Execute _
"INSERT INTO Test (CheckInDate," & _
" CheckOutDate) " & vbCr & "VALUES (#2006-09-10" & _
" 00:00:00#, #2006-09-11 00:00:00#);"
.Execute _
"INSERT INTO Test (CheckInDate," & _
" CheckOutDate) " & vbCr & "VALUES (#2006-09-12" & _
" 00:00:00#, NULL);"
.Execute _
"CREATE PROCEDURE ProcTest (start_date" & _
" DATETIME, end_date DATETIME)" & _
" AS" & vbCr & "SELECT CheckInDate, CheckOutDate," & _
" " & vbCr & "DATEDIFF('D', " & vbCr & " IIF(CheckInDate" & _
" > start_date, CheckInDate, start_date)," & _
" " & vbCr & " IIF(" & vbCr & " IIF(CheckOutDate" & _
" IS NULL, DATE(), CheckOutDate)" & _
" " & vbCr & " > end_date, end_date, " & vbCr & " " & _
" IIF(CheckOutDate IS NULL," & _
" DATE(), CheckOutDate)" & vbCr & " )" & vbCr & ")" & _
" AS Nights" & vbCr & "FROM Test" & vbCr & "WHERE start_date" & _
" < end_date;"
Dim rs
Set rs = .Execute( _
"EXECUTE ProcTest #2006-09-01 00:00:00#," & _
" #2006-09-30 00:00:00#;")
MsgBox rs.GetString(, , , , "<<NULL>>")
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Allen Browne]

So if someone checks in on 1/31/07, and checks out on 2/1/07, that counts
only as the night of Jan 31?

If so, try subtracting 1 from the check out date:
Between Table1.CheckInDate And Nz(Table1.CheckOutDate - 1, Date())

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Your suggestions are good and give me the correct format but not the
numbers
are come back higher, I believe the calculation is counting the times it
sees
the dates and not taking into account that it should be calculating the
nights. I.e. CheckOutDate-CheckInDate. Any suggestions.
--
Rose

--
Rose

Rose, the simplest way to do this will be to create a table of dates, and
then use it in a query to give you a record for each night of the
booking.
You can then turn it into a Totals query to group by the month, and give
the
count of nights booked for each month.

1. Create a table with one date/time field named (say) TheDate.
Mark the field as primary key.
Save the table as tblDate.
Enter records for all the dates you could need.
(Use the function below to enter dates programmatically if you wish.

2. Create a query using both tblDate and your existing table (called
Table1
in the example below.)
There must be no line joining the tables in the upper pane of query
design.
This is called a Cartesian Product, and gives you every combination.
Drag tblDate.TheDate into the grid, and enter this criteria under it:
Between Table1.CheckInDate And Nz(Table1.CheckOutDate, Date())
This gives you a record for each day in the range.

3. Depress the Total icon on the toolbar (upper sigma icon).
Access adds a Total row to the grid.
In the Total row under tblDate.TheDate, choose Where.
If you want to limit results to a particular month, add tblDate.TheDate
again, choose Where, and enter your limiting dates as criteria.

4. Enter this expression into a fresh column in the Field row:
TheYear: Year(tblDate.TheDate)
Accept Group By in the Total row.

5. Enter this expression into a fresh column in the Field row:
TheMonth: Month(tblDate.TheDate)
Accept Group By in the Total row.

6. Drag the primary key from Table1 into the grid.
In the Total row, under this field choose Count.

The query output now shows the number of nights booked for each month.

Here's the function that can populate the dates for you.
To add all dates between 1/1/2007 and 12/31/2010:
a) Press Ctrl+G to open the Immediate window.
b) Enter:
? MakeDates(#1/1/2007#, #12/31/2010#)


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


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Currently I have a query that states
Nights:[CheckOutDate]-[CheckInDate].
The problem is that I am putting a parameter of Between two dates in
the
checkin field.

Nights: IIf(DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is
Null,Date(),[CheckOutDate]))=0,1,DateDiff("d",[CheckInDate],IIf([CheckOutDate]
Is Null,Date(),[CheckOutDate])))

My challenge is if I input Between 09/01/2006 and 09/30/2006. How can
I
only count the days in September and not those dates that may have
happend
prior for example if people checked in on August 28 and Check out on
September 3. I would only want the query to count the two nights in
September. Also if they
checked in on September 29 and Checked Out on October 4th I would only
want
the days in September. Any Ideas.

Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006