Avg #of days between two dates (minus the month requested)

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

Guest

I know confusing
My report has a StartDate , DaysIncarcerated, DateRelease

In the DateReleased field I have the expression =[StartDate]+[DaysIncarcerated] which works fantastic!!

The problem I am having is when a person is incarcerated in 01/23/04 [StartDate] and is released 02/03/04 [DateReleased] the number of days is 12 days, my boss would like, if possible, to be able to make a field that reports the average number of days incarcerated for a certain month

I would also have to be able to have the requester type in the dates requested.

For the above example I would need the number used for calculations to be 9 days not 12

ANY HELP.....
 
I think basicaly you want to be able to print a report
indicating the number of days someone was incarcerated
during a specific month.
You may try using the folowing query
This one was based on a table named Incarcerated with 3
fields, Inmate, DateIn and DateOut.

SELECT Incarcerated.Inmate, Incarcerated.DateIn,
Incarcerated.DateOut, Year([DateIn]) AS YearIn, Year
([DateOut]) AS YearOut, Month([DateIn]) AS MonthIn, Month
([DateOut]) AS MonthOut, IIf([DateIn]<[Enter Month]
& "/1/" & [Enter Year (4 digit)],[Enter Month] & "/1/" &
[Enter Year (4 digit)],[DateIn]) AS DateStart, IIf
([DateOut]>[Enter Month] & "/" & [Last Day of Month] & "/"
& [Enter Year (4 digit)],[Enter Month] & "/" & [Last Day
of Month] & "/" & [Enter Year (4 digit)],[DateOut]) AS
DateEnd, DateDiff("d",[DateStart],[DateEnd])+1 AS DaysIncar
FROM Incarcerated
WHERE (((Year([DateIn]))<=[Enter Year (4 digit)]) AND
((Year([DateOut]))>=[Enter Year (4 digit)]) AND ((Month
([DateIn]))<=[Enter Month]) AND ((Month([DateOut]))>=
[Enter Month]));

Give it a try, I think it will work for you.
Fons

-----Original Message-----
I know confusing:
My report has a StartDate , DaysIncarcerated, DateReleased

In the DateReleased field I have the expression =
Click to expand...
[StartDate]+[DaysIncarcerated] which works fantastic!!!
The problem I am having is when a person is incarcerated
Click to expand...
in 01/23/04 [StartDate] and is released 02/03/04
[DateReleased] the number of days is 12 days, my boss
would like, if possible, to be able to make a field that
reports the average number of days incarcerated for a
certain month.
I would also have to be able to have the requester type in the dates requested..

For the above example I would need the number used for
Click to expand...
calculations to be 9 days not 12.
 
Regina.
Yes you will have to adjust according to your naming
convention.
It is a rather involved query so it may be good to create
a small table first with the names I used, and put some
dates in it. Just so you can see how my query works.
Good Luck.
Fons
 
Frons,
I made a table as you suggested and attempted to to your instructions I get the following

I tried your suggestion and it came back with "check the subquery syntax and enclose the subquery in parenthes
I tried many things and I am getting various errors

Regina
 
Back
Top