Sum Function on a Field created as part of the report

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

In a report, I created a text box named "Anticipated" ... this text box
calculates information from other fields in the report
{=([premium]*[comm]/365)*((Now()-30)-[eff date])}. I would like to create a
new text box to Sum the total of the "Anticipated" boxes.

Here is how it is grouped;

Name Header
Eff Date Header
[Anticipated]
Detail
Name Footer
[SumAnticipated]

The SumAnticipated text box prints empty ... I am sure it is how I am
referring to the [Anticipated] text box, but I have been unable to get
around it. I have tried =Sum([Reports]![rptExpected Commissions
Summary].[Anticipated]) and other variations, but I am missing something.

Your help is very much appreciated!

Thanks,

Tom
 
Tom,
You will need to repeat the entire Anticipated expression again.

=Sum([premium]*[comm]/365)*((Now()-30)-[eff date]))

Also, as you are only interested in the date - 30, not the date and
time -30, I would suggest you use *((Date()-30) in the expression, not
Now().
 
Thank you for the Date function.

The result of this formula is significantly higher than expected;

=Sum(([premium]*[comm]/365)*((Date()-30)-[eff date])) results in a total of
$4,309,804

=Sum([premium]*[comm]/365)*((Now()-30)-[eff date]) results in a total of
$2,831,523

Manually adding the report results in an acutal total of $410,677

My thought was that the problem with this was caused becuase of the multiple
detail records with varying effective dates ... but I can't seem to wrap my
mind around it.



Fredg said:
Tom,
You will need to repeat the entire Anticipated expression again.

=Sum([premium]*[comm]/365)*((Now()-30)-[eff date]))

Also, as you are only interested in the date - 30, not the date and
time -30, I would suggest you use *((Date()-30) in the expression, not
Now().

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Tom said:
In a report, I created a text box named "Anticipated" ... this text box
calculates information from other fields in the report
{=([premium]*[comm]/365)*((Now()-30)-[eff date])}. I would like to
Click to expand...
create
a
new text box to Sum the total of the "Anticipated" boxes.

Here is how it is grouped;

Name Header
Eff Date Header
[Anticipated]
Detail
Name Footer
[SumAnticipated]

The SumAnticipated text box prints empty ... I am sure it is how I am
referring to the [Anticipated] text box, but I have been unable to get
around it. I have tried =Sum([Reports]![rptExpected Commissions
Summary].[Anticipated]) and other variations, but I am missing something.

Your help is very much appreciated!

Thanks,

Tom
Click to expand...
Click to expand...
 
Back
Top