(Msg. 1) Posted: Sun Aug 31, 2008 10:12 pm
Post subject: DateDiff Archived from groups: microsoft>public>access>reports (more info?)
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
.......
(Msg. 2) Posted: Mon Sep 01, 2008 8:58 am
Post subject: Re: DateDiff [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Liliane" <Liliane DeleteThis @discussions.microsoft.com> wrote in message
news:027F55C9-8DDC-4A43-A117-0E92EE62D519@microsoft.com...
> 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!!!
(Msg. 3) Posted: Mon Sep 01, 2008 9:29 am
Post subject: Re: DateDiff [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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:
"Liliane" <Liliane.DeleteThis@discussions.microsoft.com> wrote in message
news:027F55C9-8DDC-4A43-A117-0E92EE62D519@microsoft.com...
> 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!!!
(Msg. 4) Posted: Mon Sep 01, 2008 6:06 pm
Post subject: Re: DateDiff [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"Ken Snell (MVP)" wrote:
> 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>
>
>
> "Liliane" <Liliane RemoveThis @discussions.microsoft.com> wrote in message
> news:027F55C9-8DDC-4A43-A117-0E92EE62D519@microsoft.com...
> > 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!!!
>
>
>
(Msg. 5) Posted: Tue Sep 02, 2008 9:52 am
Post subject: Re: DateDiff [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
"Liliane" wrote:
> 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.
>
> "Ken Snell (MVP)" wrote:
>
> > 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>
> >
> >
> > "Liliane" <Liliane.TakeThisOut@discussions.microsoft.com> wrote in message
> > news:027F55C9-8DDC-4A43-A117-0E92EE62D519@microsoft.com...
> > > 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!!!
> >
> >
> >
(Msg. 6) Posted: Tue Sep 02, 2008 4:24 pm
Post subject: Re: DateDiff [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
> 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
>
>
> "Liliane" wrote:
>
> > 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.
> >
> > "Ken Snell (MVP)" wrote:
> >
> > > 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>
> > >
> > >
> > > "Liliane" <Liliane RemoveThis @discussions.microsoft.com> wrote in message
> > > news:027F55C9-8DDC-4A43-A117-0E92EE62D519@microsoft.com...
> > > > 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!!!
> > >
> > >
> > >
(Msg. 7) Posted: Tue Sep 02, 2008 4:24 pm
Post subject: Re: DateDiff [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
> 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
>
>
> "Liliane" wrote:
>
> > 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.
> >
> > "Ken Snell (MVP)" wrote:
> >
> > > 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>
> > >
> > >
> > > "Liliane" <Liliane.RemoveThis@discussions.microsoft.com> wrote in message
> > > news:027F55C9-8DDC-4A43-A117-0E92EE62D519@microsoft.com...
> > > > 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!!!
> > >
> > >
> > >
(Msg. 8) Posted: Wed Sep 03, 2008 8:51 am
Post subject: Re: DateDiff [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" wrote:
> 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" wrote:
>
> > 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
> >
> >
> > "Liliane" wrote:
> >
> > > 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.
> > >
> > > "Ken Snell (MVP)" wrote:
> > >
> > > > 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>
> > > >
> > > >
> > > > "Liliane" <Liliane RemoveThis @discussions.microsoft.com> wrote in message
> > > > news:027F55C9-8DDC-4A43-A117-0E92EE62D519@microsoft.com...
> > > > > 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!!!
> > > >
> > > >
> > > >
All times are: Eastern Time (US & Canada) (change) Goto page 1, 2
Page 1 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other