Create a table CountNumber with field named CountNUM containing numbers from
0 (zero) through your maximum months spread.
SELECT Liliane.Audit_No, Liliane.Item_No,
Format(DateAdd("m",[CountNUM],[When]),"mmm yy") AS Period,
DateDiff("d",[When],(DateAdd("m",[CountNUM]+1,[When])-Day(DateAdd("m",[CountNUM]+1,[When])))) AS [Total No of Days Overdue]
FROM CountNumber, Liliane
WHERE (((Liliane.When)<[Status_Date]) AND ((Liliane.Date_Actioned) Is Not
Null) AND
((Format(DateAdd("m",[CountNUM],[When]),"yyyymm"))<=Format(Date(),"yyyymm")))
GROUP BY Liliane.Audit_No, Liliane.Item_No,
Format(DateAdd("m",[CountNUM],[When]),"mmm yy"),
DateDiff("d",[When],(DateAdd("m",[CountNUM]+1,[When])-Day(DateAdd("m",[CountNUM]+1,[When])))), Format(DateAdd("m",[CountNUM],[When]),"yyyymm")
ORDER BY Liliane.Audit_No, Liliane.Item_No,
Format(DateAdd("m",[CountNUM],[When]),"yyyymm");
--
KARL DEWEY
Build a little - Test a little
Liliane said:
In my table, there's a field named [When].
Numbers of days overdue = the end of the month - [when].
And for example, if there're two tasks overdue in January for 10 and 15
days, the total numbers of days overdue = 10 + 15 = 25 days.
If an item's [When] date < [Status_Date] and [Date_Actioned] is not null, we
can say this item is overdue.
My report needs to list months and their total numbers of days overdue.
The table looks like this:
Audit_No Item_No When Date_Actioned Status_Date
01 01 01-Jan-00 09-Jan-08
01R/05669 01 28-Oct-03 28-Oct-03 11-Feb-08
01R/05669 02 28-Oct-03 28-Oct-03 11-Nov-08
01R/05669 03 28-Oct-03 28-Oct-03 12-Nov-08
01R/05669 04 30-Oct-03 11-Apr-03 11-Aug-08
01R/05669 05 28-Oct-03 28-Oct-03 11-Feb-08
KARL DEWEY said:
So your form has ToDate and FromDate. What data is in your table? What are
the field names? Calculate total numbers of days overdue using what date?
Post sample data.
--
KARL DEWEY
Build a little - Test a little
:
ummm....My problem now is:
Users can define a period in a form (frmReports) by choosing ToDate and
FromDate. And then my report needs to list every month within the period,
and calculate total numbers of days overdue for each month that is the end of
a month minus [when] date.
So I don't know how to create a query or codes for this requirement.
Thanks a million.
:
You can express the last day of a particular month using the DateSerial
function and a 'slight trick'. For example, to show the last day of February
2008:
LastDateOfFebruary = DateSerial(2008, 2 + 1, 0)
The "trick" is to use the zeroth day of the succeeding month. In more
generic terms:
LastDateOfMonth = DateSerial(YearInteger, MonthInteger + 1, 0)
--
Ken Snell
<MS ACCESS MVP>
My report needs to calculate the difference between a date [when] and the
end
of each month. I think I need DateDiff(), but how can I describe the
second
date parameter?
My report looks like this:
Period Total No. of Days Overdue
Jan 08 0
Feb 08 0
Mar 08 0
......
Thanks a lot!!!
