Date Calculation Challenge

[Guest]

I am working with a Microsoft Access Database helping a non profit
organization try to figure out how many nights in a month people are staying
in there shelter. I have two fields a Check In Date and Check Out Date
Field. Therefore to figure out the nights a person stayed, I minus the Check
Out by the Check In Date.

The problem is if they check in the prior month, let’s say February and
checked out in March, How I can I calculate just the days in March. The
other problem is if they checked in March and have not checked out yet (null
field), how do I calculate the number of days in March. This is time
sensitive any assistance anyone can give is greatly appreciated.

 

[Duane Hookom]

I would create a table of dates "tblDates" with a date field "TheDate" and
enter all dates from the earliest to the latest dates you need. Then create
a query with your table and set the criteria under TheDate to:
Between [Check In Date] And [Check Out Date]
Change this to a totals query and add a column like:

Field: YM: Format([TheDate],"yyyymm")
Total: Group By

Set the Total: to "Where" for the TheDate field so it will not display.

You can now count or whatever to get the total by month.

 

[Guest]

Duane,

Thanks for this information. It works about 80% of the challenge. The only
problem left is those who have not check out yet and have a blank Check Out
Date. Is there a way to write an if statement or such that would say if
checkout date null, calculate by the last day of a month?


--
Rose

I would create a table of dates "tblDates" with a date field "TheDate" and
enter all dates from the earliest to the latest dates you need. Then create
a query with your table and set the criteria under TheDate to:
Between [Check In Date] And [Check Out Date]
Change this to a totals query and add a column like:

Field: YM: Format([TheDate],"yyyymm")
Total: Group By

Set the Total: to "Where" for the TheDate field so it will not display.

You can now count or whatever to get the total by month.

--
Duane Hookom
MS Access MVP

I am working with a Microsoft Access Database helping a non profit
organization try to figure out how many nights in a month people are
staying
in there shelter. I have two fields a Check In Date and Check Out Date
Field. Therefore to figure out the nights a person stayed, I minus the
Check
Out by the Check In Date.

The problem is if they check in the prior month, let's say February and
checked out in March, How I can I calculate just the days in March. The
other problem is if they checked in March and have not checked out yet
(null
field), how do I calculate the number of days in March. This is time
sensitive any assistance anyone can give is greatly appreciated.

 

[John Vinson]

Thanks for this information. It works about 80% of the challenge. The only
problem left is those who have not check out yet and have a blank Check Out
Date. Is there a way to write an if statement or such that would say if
checkout date null, calculate by the last day of a month?

NZ([Check Out Date], DateSerial(Year(Date()), Month(Date()) + 1, 0))

will return the check out date if it exists, and the last date of the
current month if it's NULL.

John W. Vinson[MVP]