Is there a way to break down dates by month in a report?

[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]

The usual way to handle this is to get Access to generate a record for each
date in the range. You can then group/count the number of dates in any
period.

These dates have to come from somewhere, so you create a table of dates, and
use it in your query.

1. Create a table with just one Date/Time field named TheDate.
Mark is as the primary key.
Save the table with the name tblDate.

2. Use the function below to populate all dates in the range you could need.
For example, to enter the next 10 years, open the Immediate Window (Ctrl+G),
and enter:
? MakeDates(#1/1/2007#, #1/1/2018#)

3. Create a query that uses your existing table and tblDate.
There must be no line joining the 2 tables in the upper pane of table
design.
Drag tblDate.TheDate into the grid, and enter this in the Criteria row:
Between [CheckInDate] And [CheckOutDate]
The query now gives you a record for every date.

From there, you can use the Totals button in the toolbar of query design to
group the query by year and month. Alternatively, you can use the Sorting
And Grouping dialog in report design to group by the month and get your
counts. Post a reply if you get the query working but are not sure how to
proceed.

And here's the code to populate the table without having to type in all the
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

 

[Guest]

Hi Allen,

Thanks for the work so far. I was able to create the tables and queries.
However, the numbers are coming up to high. As if it is calculating the
dates (Days) as opposed to calculating the nights. Any ideas?
--
Rose

The usual way to handle this is to get Access to generate a record for each
date in the range. You can then group/count the number of dates in any
period.

These dates have to come from somewhere, so you create a table of dates, and
use it in your query.

1. Create a table with just one Date/Time field named TheDate.
Mark is as the primary key.
Save the table with the name tblDate.

2. Use the function below to populate all dates in the range you could need.
For example, to enter the next 10 years, open the Immediate Window (Ctrl+G),
and enter:
? MakeDates(#1/1/2007#, #1/1/2018#)

3. Create a query that uses your existing table and tblDate.
There must be no line joining the 2 tables in the upper pane of table
design.
Drag tblDate.TheDate into the grid, and enter this in the Criteria row:
Between [CheckInDate] And [CheckOutDate]
The query now gives you a record for every date.

From there, you can use the Totals button in the toolbar of query design to
group the query by year and month. Alternatively, you can use the Sorting
And Grouping dialog in report design to group by the month and get your
counts. Post a reply if you get the query working but are not sure how to
proceed.

And here's the code to populate the table without having to type in all the
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

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

 

[Allen Browne]

I'm not sure I understand the difference between 'days' and 'nights'.

Perhaps when somone:
- books in on April 30th, and
- books out on May 1st
you need to count this as 1 night, not 2 days?

If so, do you count this night an April booking or a May booking?

If April, you might try changing the criteria like this:

= [CheckInDate] And < [CheckOutDate]

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

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

Hi Allen,

Thanks for the work so far. I was able to create the tables and queries.
However, the numbers are coming up to high. As if it is calculating the
dates (Days) as opposed to calculating the nights. Any ideas?
--
Rose

The usual way to handle this is to get Access to generate a record for
each
date in the range. You can then group/count the number of dates in any
period.

These dates have to come from somewhere, so you create a table of dates,
and
use it in your query.

1. Create a table with just one Date/Time field named TheDate.
Mark is as the primary key.
Save the table with the name tblDate.

2. Use the function below to populate all dates in the range you could
need.
For example, to enter the next 10 years, open the Immediate Window
(Ctrl+G),
and enter:
? MakeDates(#1/1/2007#, #1/1/2018#)

3. Create a query that uses your existing table and tblDate.
There must be no line joining the 2 tables in the upper pane of table
design.
Drag tblDate.TheDate into the grid, and enter this in the Criteria row:
Between [CheckInDate] And [CheckOutDate]
The query now gives you a record for every date.

From there, you can use the Totals button in the toolbar of query design
to
group the query by year and month. Alternatively, you can use the Sorting
And Grouping dialog in report design to group by the month and get your
counts. Post a reply if you get the query working but are not sure how to
proceed.

And here's the code to populate the table without having to type in all
the
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

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

 

[Guest]

Allen,

couldn't you use a format date to just display month in a query and group by
that?

I'm not sure I understand the difference between 'days' and 'nights'.

Perhaps when somone:
- books in on April 30th, and
- books out on May 1st
you need to count this as 1 night, not 2 days?

If so, do you count this night an April booking or a May booking?

If April, you might try changing the criteria like this:

= [CheckInDate] And < [CheckOutDate]

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

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

Hi Allen,

Thanks for the work so far. I was able to create the tables and queries.
However, the numbers are coming up to high. As if it is calculating the
dates (Days) as opposed to calculating the nights. Any ideas?
--
Rose

The usual way to handle this is to get Access to generate a record for
each
date in the range. You can then group/count the number of dates in any
period.

These dates have to come from somewhere, so you create a table of dates,
and
use it in your query.

1. Create a table with just one Date/Time field named TheDate.
Mark is as the primary key.
Save the table with the name tblDate.

2. Use the function below to populate all dates in the range you could
need.
For example, to enter the next 10 years, open the Immediate Window
(Ctrl+G),
and enter:
? MakeDates(#1/1/2007#, #1/1/2018#)

3. Create a query that uses your existing table and tblDate.
There must be no line joining the 2 tables in the upper pane of table
design.
Drag tblDate.TheDate into the grid, and enter this in the Criteria row:
Between [CheckInDate] And [CheckOutDate]
The query now gives you a record for every date.

From there, you can use the Totals button in the toolbar of query design
to
group the query by year and month. Alternatively, you can use the Sorting
And Grouping dialog in report design to group by the month and get your
counts. Post a reply if you get the query working but are not sure how to
proceed.

And here's the code to populate the table without having to type in all
the
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

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

 

[Guest]

Hi Chris,
A group by only calculates the one date someone checked in not the entire
time they stayed.
--
Rose

Allen,

couldn't you use a format date to just display month in a query and group by
that?

I'm not sure I understand the difference between 'days' and 'nights'.

Perhaps when somone:
- books in on April 30th, and
- books out on May 1st
you need to count this as 1 night, not 2 days?

If so, do you count this night an April booking or a May booking?

If April, you might try changing the criteria like this:

= [CheckInDate] And < [CheckOutDate]

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

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

Hi Allen,

Thanks for the work so far. I was able to create the tables and queries.
However, the numbers are coming up to high. As if it is calculating the
dates (Days) as opposed to calculating the nights. Any ideas?
--
Rose


:

The usual way to handle this is to get Access to generate a record for
each
date in the range. You can then group/count the number of dates in any
period.

These dates have to come from somewhere, so you create a table of dates,
and
use it in your query.

1. Create a table with just one Date/Time field named TheDate.
Mark is as the primary key.
Save the table with the name tblDate.

2. Use the function below to populate all dates in the range you could
need.
For example, to enter the next 10 years, open the Immediate Window
(Ctrl+G),
and enter:
? MakeDates(#1/1/2007#, #1/1/2018#)

3. Create a query that uses your existing table and tblDate.
There must be no line joining the 2 tables in the upper pane of table
design.
Drag tblDate.TheDate into the grid, and enter this in the Criteria row:
Between [CheckInDate] And [CheckOutDate]
The query now gives you a record for every date.

From there, you can use the Totals button in the toolbar of query design
to
group the query by year and month. Alternatively, you can use the Sorting
And Grouping dialog in report design to group by the month and get your
counts. Post a reply if you get the query working but are not sure how to
proceed.

And here's the code to populate the table without having to type in all
the
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

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]

Perhaps I have not understood correctly, Chris?

To give an example, this is what I assumed Rose wanted. If you booked in on
April 29, and booked out on June 4, I think she wants the query to show the
booking like this:
April 2 nights (i.e. 29th and 30th)
May 31 nights (all of them)
June 3 nights (1st, 2nd, and 3rd)

If that's the goal, you can't just format the month: the booking above is
mostly in May, but there is no May in either the CheckInDate or the
CheckOutDate.

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

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

Allen,

couldn't you use a format date to just display month in a query and group
by
that?

I'm not sure I understand the difference between 'days' and 'nights'.

Perhaps when somone:
- books in on April 30th, and
- books out on May 1st
you need to count this as 1 night, not 2 days?

If so, do you count this night an April booking or a May booking?

If April, you might try changing the criteria like this:

= [CheckInDate] And < [CheckOutDate]

Hi Allen,

Thanks for the work so far. I was able to create the tables and
queries.
However, the numbers are coming up to high. As if it is calculating
the
dates (Days) as opposed to calculating the nights. Any ideas?
--
Rose


:

The usual way to handle this is to get Access to generate a record for
each
date in the range. You can then group/count the number of dates in any
period.

These dates have to come from somewhere, so you create a table of
dates,
and
use it in your query.

1. Create a table with just one Date/Time field named TheDate.
Mark is as the primary key.
Save the table with the name tblDate.

2. Use the function below to populate all dates in the range you could
need.
For example, to enter the next 10 years, open the Immediate Window
(Ctrl+G),
and enter:
? MakeDates(#1/1/2007#, #1/1/2018#)

3. Create a query that uses your existing table and tblDate.
There must be no line joining the 2 tables in the upper pane of table
design.
Drag tblDate.TheDate into the grid, and enter this in the Criteria
row:
Between [CheckInDate] And [CheckOutDate]
The query now gives you a record for every date.

From there, you can use the Totals button in the toolbar of query
design
to
group the query by year and month. Alternatively, you can use the
Sorting
And Grouping dialog in report design to group by the month and get
your
counts. Post a reply if you get the query working but are not sure how
to
proceed.

And here's the code to populate the table without having to type in
all
the
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

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