p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Date Difference function


Message #1 by dfuas@h... on Wed, 19 Jun 2002 16:38:25
Hi,

I am trying to add a field in a report that calculates the number of days 
that a record has been in the system for each record reference number.
I have this: DateDiff("d",Now(),[Payment Date])
I don?t know how to fit this so that for each reference number, I get the 
actual number of days using that function.
The report results are displayed using a query.
Right now I have that function on a text box control source for the field 
and when I try to preview the report I get an input form asking for the 
[Payment Date] which then works for the entire report although each record 
has its own  [Payment Date]
Please help if you can
Thank you

Dinis
Message #2 by "Charlie Goodwin" <cgoodwin@c...> on Wed, 19 Jun 2002 23:27:59 -0400
Try putting that same expression in the query the report is generated from,
 rather than in a textbox within the report.   It seems like Access reports
 "like" calculations done in the underlying query, rather than in textboxes
 in the report.

Build it something like this...add a new column in your query then enter th
is in the Field Row

RecordAge:DateDiff("d",Now(),[Payment Date])

When your query runs it should  (if this Access newby has it right)  yield 
a field for each record in your recordset showing the age of that record.

If you type it in as I have shown, you should see a new field called Record
Age that you can include in your report where you want the date difference 
to show.

Charlie





> Hi,
> 
> I am trying to add a field in a report that calculates the number of days
> that a record has been in the system for each record reference number.
> I have this: DateDiff("d",Now(),[Payment Date])
> I don=92t know how to fit this so that for each reference number, I get t
he
> actual number of days using that function.
> The report results are displayed using a query.
> Right now I have that function on a text box control source for the field
> and when I try to preview the report I get an input form asking for the
> [Payment Date] which then works for the entire report although each record
> has its own  [Payment Date]
> Please help if you can
> Thank you
> 
> Dinis

Message #3 by "Charlie Goodwin" <cgoodwin@c...> on Wed, 19 Jun 2002 23:35:51 -0400
PS... theres nothing sacred about " RecordAge " as a field name.   It just 
sounded convenient and easy to remember.   If you do not make up your own n
ame, Access will make up brief, boring, and hard to remember names like "Ex
pr1", "Expr2"  etc.   You can just type in your own better names and it wil
l be much easier to sort out what goes where, and to troubleshoot.

Charlie




> Hi,
>
> I am trying to add a field in a report that calculates the number of days
> that a record has been in the system for each record reference number.
> I have this: DateDiff("d",Now(),[Payment Date])
> I don=92t know how to fit this so that for each reference number, I get t
he
> actual number of days using that function.
> The report results are displayed using a query.
> Right now I have that function on a text box control source for the field
> and when I try to preview the report I get an input form asking for the
> [Payment Date] which then works for the entire report although each record
> has its own  [Payment Date]
> Please help if you can
> Thank you
>
> Dinis

Message #4 by "Amy Wyatt" <amyw@c...> on Thu, 20 Jun 2002 16:14:53
The error (i.e. the pop-up asking for the value of [Payment Date] is 
because the recordsource bound to your report cannot find a field 
called "Payment Date". Check your recordsource and see if it may be 
spelled different or does not have a space between or is missing 
altogether. If it is there and spelled correctly you will have to tell me 
where the textbox is located in the report (which section (detail, header 
etc.)) to determine if there is another problem occuring. Otherwise the 
function =DateDiff("d",Now(),[Payment Date]) in the Control Source of the 
textbox should work (although it will give a negative number). Another way 
to do it is simply to enter =Date()-[Payment Date] in the text box Control 
Source. If you are simply needing days between dates you do not 
necessarily need the DateDiff function. 

Just a tip, be wary of using Now() when all you want is the date part of 
the system date. Get used to using the Date() function instead when time 
is not needed because otherwise you may run into problems later in other 
date comparisons.

Hope this helps,

Amy

> Try putting that same expression in the query the report is generated 
from,
 rather than in a textbox within the report.   It seems like Access 
reports
 "like" calculations done in the underlying query, rather than in 
textboxes
 in the report.

Build it something like this...add a new column in your query then enter 
th
is in the Field Row

RecordAge:DateDiff("d",Now(),[Payment Date])

When your query runs it should  (if this Access newby has it right)  yield 

a field for each record in your recordset showing the age of that record.

If you type it in as I have shown, you should see a new field called 
Record
Age that you can include in your report where you want the date difference 

to show.

Charlie





> Hi,
> 
> I am trying to add a field in a report that calculates the number of days
> that a record has been in the system for each record reference number.
> I have this: DateDiff("d",Now(),[Payment Date])
> I don=92t know how to fit this so that for each reference number, I get 
t
he
> actual number of days using that function.
> The report results are displayed using a query.
> Right now I have that function on a text box control source for the field
> and when I try to preview the report I get an input form asking for the
> [Payment Date] which then works for the entire report although each 
record
> has its own  [Payment Date]
> Please help if you can
> Thank you
> 
> Dinis

Message #5 by "Charlie Goodwin" <cgoodwin@c...> on Thu, 20 Jun 2002 13:43:18 -0400
Amy ... or anyone else,

Newby question....
I have been doing pretty much all of my report calculations
in the report's underlying querys, rather than in the reports
themselves, since some kind of involved calculations just
wouldn't work when I did them in the report.   Is there any
relative benefit to choosing either the query or the report
for where to perform calculations when either choice will
work?

Charlie 



> The error (i.e. the pop-up asking for the value of [Payment Date] is
> because the recordsource bound to your report cannot find a field
> called "Payment Date". Check your recordsource and see if it may be
> spelled different or does not have a space between or is missing
> altogether. If it is there and spelled correctly you will have to tell me
> where the textbox is located in the report (which section (detail, header
> etc.)) to determine if there is another problem occuring. Otherwise the
> function =3DDateDiff("d",Now(),[Payment Date]) in the Control Source of t
he
> textbox should work (although it will give a negative number). Another way
> to do it is simply to enter =3DDate()-[Payment Date] in the text box Cont
rol
> Source. If you are simply needing days between dates you do not
> necessarily need the DateDiff function.
> 
> Just a tip, be wary of using Now() when all you want is the date part of
> the system date. Get used to using the Date() function instead when time
> is not needed because otherwise you may run into problems later in other
> date comparisons.
> 
> Hope this helps,
> 
> Amy
> 
> > Try putting that same expression in the query the report is generated
> from,=3D
>  rather than in a textbox within the report.   It seems like Access
> reports=3D
>  "like" calculations done in the underlying query, rather than in
> textboxes=3D
>  in the report.
> 
> Build it something like this...add a new column in your query then enter
> th=3D
> is in the Field Row
> 
> RecordAge:DateDiff("d",Now(),[Payment Date])
> 
> When your query runs it should  (if this Access newby has it right)  yield
> =3D
> a field for each record in your recordset showing the age of that record.
> 
> If you type it in as I have shown, you should see a new field called
> Record=3D
> Age that you can include in your report where you want the date difference
> =3D
> to show.
> 
> Charlie
> 
> 
> 
> 
> 
> > Hi,
> >
> > I am trying to add a field in a report that calculates the number of da
ys
> > that a record has been in the system for each record reference number.
> > I have this: DateDiff("d",Now(),[Payment Date])
> > I don=3D92t know how to fit this so that for each reference number, I g
et
> t=3D
> he
> > actual number of days using that function.
> > The report results are displayed using a query.
> > Right now I have that function on a text box control source for the fie
ld
> > and when I try to preview the report I get an input form asking for the
> > [Payment Date] which then works for the entire report although each
> record
> > has its own  [Payment Date]
> > Please help if you can
> > Thank you
> >
> > Dinis
> 

Message #6 by "cdebiasio@t... on Thu, 20 Jun 2002 20:47:37 +0200 (CEST)
Hi!

Well, generally speaking, I prefer to do so, that's to say, keep the reports as 
much clean as I can, delegating so every calculation to the underlying queries. 
Basically, you have several benefits doing so: first, you rely on a data 
engine, that's obviously better than an interpreted XML or whatever else a 
report manager can be. This means it can group, sum, total, average, and 
perform operations much better and quicker than reports managers, especially if 
you are using an SQL Server as a backend. Second, that way you can focus on 
data grouping, sorting and formatting, and not on mixed problems. Although the 
report managers allow us more and more power and flexibility, they are 
conceptually designed to offer nice and effective RAPRESENTATION of data, not 
DATA TRANSFORMATION.
Also! Any change in data (shape, size, type) can be managed from a single 
point, and the report still will find the data usable. That's to say, you won't 
have to change BOTH the data AND the report. This is very important, especially 
when a data source is used in a sophisticated way in conjunction with several 
reports.
As usual, keeping the whole thing simple and clear helps a lot. Keeping 
the "code", or the "logic", to extract complex data (or combine simple data 
pieces into something more complex) in the db engine, to me, simplifies our 
life.
These are my opinions, though. I know other consultants who prefer different 
solutions.

   Claudio de Biasio
     Team 97 S.r.l.


Quoting Charlie Goodwin <cgoodwin@c...>:

> Amy ... or anyone else,
> 
> Newby question....
> I have been doing pretty much all of my report calculations
> in the report's underlying querys, rather than in the reports
> themselves, since some kind of involved calculations just
> wouldn't work when I did them in the report.   Is there any
> relative benefit to choosing either the query or the report
> for where to perform calculations when either choice will
> work?
> 
> Charlie 
> 
> 
> 
> > The error (i.e. the pop-up asking for the value of [Payment Date] is
> > because the recordsource bound to your report cannot find a field
> > called "Payment Date". Check your recordsource and see if it may be
> > spelled different or does not have a space between or is missing
> > altogether. If it is there and spelled correctly you will have to tell
> me
> > where the textbox is located in the report (which section (detail,
> header
> > etc.)) to determine if there is another problem occuring. Otherwise
> the
> > function =DateDiff("d",Now(),[Payment Date]) in the Control Source of
> the
> > textbox should work (although it will give a negative number). Another
> way
> > to do it is simply to enter =Date()-[Payment Date] in the text box
> Control
> > Source. If you are simply needing days between dates you do not
> > necessarily need the DateDiff function.
> > 
> > Just a tip, be wary of using Now() when all you want is the date part
> of
> > the system date. Get used to using the Date() function instead when
> time
> > is not needed because otherwise you may run into problems later in
> other
> > date comparisons.
> > 
> > Hope this helps,
> > 
> > Amy
> > 
> > > Try putting that same expression in the query the report is
> generated
> > from,
> >  rather than in a textbox within the report.   It seems like Access
> > reports
> >  "like" calculations done in the underlying query, rather than in
> > textboxes
> >  in the report.
> > 
> > Build it something like this...add a new column in your query then
> enter
> > th
> > is in the Field Row
> > 
> > RecordAge:DateDiff("d",Now(),[Payment Date])
> > 
> > When your query runs it should  (if this Access newby has it right) 
> yield
> > 
> > a field for each record in your recordset showing the age of that
> record.
> > 
> > If you type it in as I have shown, you should see a new field called
> > Record
> > Age that you can include in your report where you want the date
> difference
> > 
> > to show.
> > 
> > Charlie
> > 
> > 
> > 
> > 
> > 
> > > Hi,
> > >
> > > I am trying to add a field in a report that calculates the number of
> days
> > > that a record has been in the system for each record reference
> number.
> > > I have this: DateDiff("d",Now(),[Payment Date])
> > > I don=92t know how to fit this so that for each reference number, I
> get
> > t
> > he
> > > actual number of days using that function.
> > > The report results are displayed using a query.
> > > Right now I have that function on a text box control source for the
> field
> > > and when I try to preview the report I get an input form asking for
> the
> > > [Payment Date] which then works for the entire report although each
> > record
> > > has its own  [Payment Date]
> > > Please help if you can
> > > Thank you
> > >
> > > Dinis
> > 
> 
> 
> 
Message #7 by joe.dunn@c... on Fri, 21 Jun 2002 09:48:10 +0000
Charlie asked...

Is there any
relative benefit to choosing either the query or the report
for where to perform calculations when either choice will
work?

and my view has always been YES but not necessarily from a speed if
processing point-of-view. I believe in checking the underlying data
accuracy and will usually confirm this in the query before worrying about
the presentation - i.e. the report. Sometimes I even write a rough query to
cross check the result and then throw away the cross check query. Then I
KNOW that the data is correct - including the calculations.



some other potential benefits include:

   The query MAY be usable as the data source in several different reports

   The report may be complex and separating the data layer (i,.e. the
   recordsource) from the presentational layer (the report) may reduce
   complexity and allow the developer to concentrate on the report layout
   knowing that the underlying data is proven to be correct


*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************
Message #8 by "Amy Wyatt" <amyw@c...> on Fri, 21 Jun 2002 15:17:31
I agree that doing calculation in the query makes sense but there are 
times when it actually makes more sense doing the calculations in the 
report. For example, if you are using the same base information query for 
multiple reports and the calculations are fairly simple and straight 
forward but different for each report, adding the calculation to the query 
either means creating multiple additional queries or adding all the 
calculations for the multiple reports to the one query making the computer 
do way more than it has to.

I really don't have a preferance and I don't think you can set a hard and 
fast rule regarding this. It is a case by case basis as to what works best 
for the situation. One thing I will say that I prefer is not saving a lot 
of queries in a DB application. I have seen people have hundreds of 
queries stored when they only use them very infrequently. I am an advocate 
of creating the query on the fly and using it rather than saving them. Of 
course this it a bit more on the advanced side using SQL and code.

Amy


> 
Charlie asked...

Is there any
relative benefit to choosing either the query or the report
for where to perform calculations when either choice will
work?

and my view has always been YES but not necessarily from a speed if
processing point-of-view. I believe in checking the underlying data
accuracy and will usually confirm this in the query before worrying about
the presentation - i.e. the report. Sometimes I even write a rough query to
cross check the result and then throw away the cross check query. Then I
KNOW that the data is correct - including the calculations.



some other potential benefits include:

   The query MAY be usable as the data source in several different reports

   The report may be complex and separating the data layer (i,.e. the
   recordsource) from the presentational layer (the report) may reduce
   complexity and allow the developer to concentrate on the report layout
   knowing that the underlying data is proven to be correct


*************************************************************************

This e-mail may contain confidential information or be privileged. It is 
intended to be read and used only by the named recipient(s). If you are 
not the intended recipient(s) please notify us immediately so that we can 
make arrangements for its return: you should not disclose the contents of 
this e-mail to any other person, or take any copies. Unless stated 
otherwise by an authorised individual, nothing contained in this e-mail is 
intended to create binding legal obligations between us and opinions 
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the 
Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R -
 for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  -
 for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 
3390839 - for ISAs and investment products bearing the CIS name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-
832-8686   Internet  http://www.cis.co.uk   E-mail cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-
operative Bank p.l.c., registered in England and Wales number 990937, P.O. 
Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS 
Policyholder Services Limited as agent of the Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

***************************************************************************
*****

  Return to Index