date - # of days calcuation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has a field for a begin date & a field for an end date.
A person may check into the shelter on 6/15 and leave on 7/15, for example.
The problem is I need to figure out how many days we supplied overnight
housing for people for the month of JUNE only. Can I do that with just these
2 fields, or should I redesign the database to put some other field in there?
 
Donna

It sounds like you are saying you need to count the number of days between a
start date (or the first of a specified month, which ever occurs last), and
an end date (or the end of a specified month, which ever occurs first).

One approach might be to create a function that you feed a month (and year),
and the start and end dates, and it returns the "days".

If you aren't comfortable with creating a procedure, you could use the IIF()
function in your query to determine the "which ever comes ..." for the pairs
of comparisons, the Month() and Year() functions, and parameters in your
query to specify the month and year.

Good luck!

Jeff Boyce
<Access MVP>
 
I have a table that has a field for a begin date & a field for an end date.
A person may check into the shelter on 6/15 and leave on 7/15, for example.
The problem is I need to figure out how many days we supplied overnight
housing for people for the month of JUNE only. Can I do that with just these
2 fields, or should I redesign the database to put some other field in there?
Click to expand...

If I understand you correctly, you wish to know how many days between
6/15 and the end of that month.

Exp:DateDiff("d",[DateField],DateSerial(Year([DateField]),Month([DateField])+1,0))

For 6/15 the answer is 15.
 
The scenario is: I need to come up with a total number of days we placed
people in beds in a month, in a quarter, in a year - what I am picturing is a
form allowing the user to put in a date range of say June 1 to June 30. The
query needs to calculate how many nights we put up people during that time
frame. So if a person was there 6/3 to 6/5 it would be 2 nights. If a second
person was there 6/28 to 7/3, it would be 3 nights - or a total of 5 nights
for both people. How would I write a query that would come up with that
result?

fredg said:
I have a table that has a field for a begin date & a field for an end date.
A person may check into the shelter on 6/15 and leave on 7/15, for example.
The problem is I need to figure out how many days we supplied overnight
housing for people for the month of JUNE only. Can I do that with just these
2 fields, or should I redesign the database to put some other field in there?
Click to expand...

If I understand you correctly, you wish to know how many days between
6/15 and the end of that month.

Exp:DateDiff("d",[DateField],DateSerial(Year([DateField]),Month([DateField])+1,0))

For 6/15 the answer is 15.
Click to expand...
 
Hi Donna

to simply count the total days you would need a query
having the field name of startdate, enddate, and a calculated for figuring
for date difference
if you are comfortable with the design view you would have entries

the datediff would be caculated

numdates: DateDiff("y",startdate, enddate)
the total would be sum

if you want a date range you would have criterea for either an entered or
fixed date range or you could do a crosstab by month/year(or qtr/year,
week/year)
a crosstab by month/year would so Jan,(month) by row and then year would be
in cols for example this way you could see
trends in specific months and time of year which might apply in this case
also you could have a calculated AVG so certain time of years may average
longer # of days

if you have more specifics I could build an example

Kevin

Donna said:
The scenario is: I need to come up with a total number of days we placed
people in beds in a month, in a quarter, in a year - what I am picturing
is a
form allowing the user to put in a date range of say June 1 to June 30.
The
query needs to calculate how many nights we put up people during that time
frame. So if a person was there 6/3 to 6/5 it would be 2 nights. If a
second
person was there 6/28 to 7/3, it would be 3 nights - or a total of 5
nights
for both people. How would I write a query that would come up with that
result?

fredg said:
I have a table that has a field for a begin date & a field for an end
date.
A person may check into the shelter on 6/15 and leave on 7/15, for
example.
The problem is I need to figure out how many days we supplied overnight
housing for people for the month of JUNE only. Can I do that with just
these
2 fields, or should I redesign the database to put some other field in
there?
Click to expand...

If I understand you correctly, you wish to know how many days between
6/15 and the end of that month.

Exp:DateDiff("d",[DateField],DateSerial(Year([DateField]),Month([DateField])+1,0))

For 6/15 the answer is 15.
Click to expand...
Click to expand...
 
You should be able to use an expression such as

DateDiff("d", IIF(StartDate < [PeriodBegin],StartDate,[PeriodBegin]),IIF(EndDate<[PeriodEnd],EndDate,[PeriodEnd]))

StartDate and EndDate are the dates in the record
PeriodBegin and PeriodEnd are the start and end dates of the comparison parameters.


The scenario is: I need to come up with a total number of days we placed
people in beds in a month, in a quarter, in a year - what I am picturing is a
form allowing the user to put in a date range of say June 1 to June 30. The
query needs to calculate how many nights we put up people during that time
frame. So if a person was there 6/3 to 6/5 it would be 2 nights. If a second
person was there 6/28 to 7/3, it would be 3 nights - or a total of 5 nights
for both people. How would I write a query that would come up with that
result?

fredg said:
I have a table that has a field for a begin date & a field for an end date.
A person may check into the shelter on 6/15 and leave on 7/15, for example.
The problem is I need to figure out how many days we supplied overnight
housing for people for the month of JUNE only. Can I do that with just these
2 fields, or should I redesign the database to put some other field in there?
Click to expand...

If I understand you correctly, you wish to know how many days between
6/15 and the end of that month.

Exp:DateDiff("d",[DateField],DateSerial(Year([DateField]),Month([DateField])+1,0))

For 6/15 the answer is 15.
Click to expand...
Click to expand...
 
I guess you can try something like:

SELECT RecordID, DateDiff( "d",
IIf([DateIn] > [PeriodFrom], [DateIn], [PeriodFrom]),
IIf([DateOut] <= [PeriodTo], [DateOut], [PeriodTo] + 1) ) As
BedDaysInPeriod
FROM [YourTable]
WHERE ([DateIn] <= [PeriodTo])
AND ([DateOut] >= [PeriodFrom])

I guess you use the Hotels' convention where DateIn is counted and DateOut
is not counted. The above should also take care of Null DateOut, i.e.
BedOccupancy is still current.

Try with [PeriodFrom] = 01/Jun/2005
[PeriodTo] = 30/Jun/2005
 
Back
Top