Dsum in Report

[Guest]

Hello
I am using the dsum in two locations of my report (the Report Footer and in
a Group Footer). I am calculating totals on an aging calculation, for
example:
=Dsum("[billing]","[Dept Billing]","Date()-[EndDate] between 0 and 15")

[Dept Billing] is the query the report is based on.

This works fine in the Report footer, but if I use the same formula in the
Group Footer I get the exact same results as the Report Footer, not the
results for this group only. I need help on the Dsum for totaling the
billing for each group.

I would appreciate any suggestions on what I am doing wrong. I hope I am
not too vague in my problem.

Thanks

 

[Duane Hookom]

Try this in any Group or Report Header or Footer Section:

=Sum(Abs(Date()-[EndDate] between 0 and 15) * [Billing])

 

[Guest]

Thank you for your response. I do not think I was clear on what I am trying
to accomplish. We have several departments and we are trying to track each
deparmtments outstanding billings by how many days they are outstanding
(Date() - [End Date]). Therefore, I am trying to sum up all the outstanding
billing that has been outstanding for 0 -15 days, 16-30 days, ect....

I was able to create the Dsum formula for all departments combined (see
formula below) but not for each deparment, which I have set up the grouping
for.

I had initially thought the same formula would work in the Group footer, but
it still gives me the totals for all depts combined.

I am sorry for being clear as mud. Does this make any sense?

Try this in any Group or Report Header or Footer Section:

=Sum(Abs(Date()-[EndDate] between 0 and 15) * [Billing])

--
Duane Hookom
MS Access MVP
--

Hello
I am using the dsum in two locations of my report (the Report Footer and
in
a Group Footer). I am calculating totals on an aging calculation, for
example:
=Dsum("[billing]","[Dept Billing]","Date()-[EndDate] between 0 and 15")

[Dept Billing] is the query the report is based on.

This works fine in the Report footer, but if I use the same formula in the
Group Footer I get the exact same results as the Report Footer, not the
results for this group only. I need help on the Dsum for totaling the
billing for each group.

I would appreciate any suggestions on what I am doing wrong. I hope I am
not too vague in my problem.

Thanks

 

[Duane Hookom]

I don't think you have told us what your "group" is. If you are grouping by
department, then the expression that I suggested should work as expected
providing the Billing total for the group where the difference between
Date() and EndDate is between 0 and 15.

--
Duane Hookom
MS Access MVP
--

Thank you for your response. I do not think I was clear on what I am
trying
to accomplish. We have several departments and we are trying to track
each
deparmtments outstanding billings by how many days they are outstanding
(Date() - [End Date]). Therefore, I am trying to sum up all the
outstanding
billing that has been outstanding for 0 -15 days, 16-30 days, ect....

I was able to create the Dsum formula for all departments combined (see
formula below) but not for each deparment, which I have set up the
grouping
for.

I had initially thought the same formula would work in the Group footer,
but
it still gives me the totals for all depts combined.

I am sorry for being clear as mud. Does this make any sense?

Try this in any Group or Report Header or Footer Section:

=Sum(Abs(Date()-[EndDate] between 0 and 15) * [Billing])

--
Duane Hookom
MS Access MVP
--

Hello
I am using the dsum in two locations of my report (the Report Footer
and
in
a Group Footer). I am calculating totals on an aging calculation, for
example:
=Dsum("[billing]","[Dept Billing]","Date()-[EndDate] between 0 and 15")

[Dept Billing] is the query the report is based on.

This works fine in the Report footer, but if I use the same formula in
the
Group Footer I get the exact same results as the Report Footer, not the
results for this group only. I need help on the Dsum for totaling the
billing for each group.

I would appreciate any suggestions on what I am doing wrong. I hope I
am
not too vague in my problem.

Thanks

 

[Guest]

Hello Duane,
I was out of commission for awhile and did not get to try this til today. I
want to than you very much. As you said, it did work. Sorry for doubting
you.

I don't think you have told us what your "group" is. If you are grouping by
department, then the expression that I suggested should work as expected
providing the Billing total for the group where the difference between
Date() and EndDate is between 0 and 15.

--
Duane Hookom
MS Access MVP
--

Thank you for your response. I do not think I was clear on what I am
trying
to accomplish. We have several departments and we are trying to track
each
deparmtments outstanding billings by how many days they are outstanding
(Date() - [End Date]). Therefore, I am trying to sum up all the
outstanding
billing that has been outstanding for 0 -15 days, 16-30 days, ect....

I was able to create the Dsum formula for all departments combined (see
formula below) but not for each deparment, which I have set up the
grouping
for.

I had initially thought the same formula would work in the Group footer,
but
it still gives me the totals for all depts combined.

I am sorry for being clear as mud. Does this make any sense?

Try this in any Group or Report Header or Footer Section:

=Sum(Abs(Date()-[EndDate] between 0 and 15) * [Billing])

--
Duane Hookom
MS Access MVP
--

Hello
I am using the dsum in two locations of my report (the Report Footer
and
in
a Group Footer). I am calculating totals on an aging calculation, for
example:
=Dsum("[billing]","[Dept Billing]","Date()-[EndDate] between 0 and 15")

[Dept Billing] is the query the report is based on.

This works fine in the Report footer, but if I use the same formula in
the
Group Footer I get the exact same results as the Report Footer, not the
results for this group only. I need help on the Dsum for totaling the
billing for each group.

I would appreciate any suggestions on what I am doing wrong. I hope I
am
not too vague in my problem.

Thanks