Date Groupings

[Guest]

I'm trying to prepare an annual activity report for each of our partner's
capital accounts. The field names are [partner name],[activity date],
[units] and [$ investment]. I have a query that pulls the partner name &
address info from one table and the activity info from another table. I
would like to be able to report 1) Beginning of year balances for units and $
investment, 2) monthly activity totals for units and $ investment and 3) end
of year totals for the items mentioned. I've tried the following expression
in the partner name page footer - iif ([activity date < #01/01/2004#, sum
([units]),0) in order to print the prior year's totals but the output is
showing totals up through today. Any help on reporting items 1 and 2 would
be appreciated.

 

[Guest]

Joel.
1) Beginning of year balances for units and $ investment
= Sum(Abs([Ativity date] < #01/01/2004#)*[Units])
= Sum(Abs([Ativity date] < #01/01/2004#)*[$ Investment])

2) monthly activity totals for units and $ investment
Create a subreport grouped by month and filtered for 2004 only.

3) end of year totals for the items mentioned
Use the same formula's as for 1) but change the 2005 to 2005.

Hope this helps.
Fons

 

[Guest]

Fons,

You're brilliant. Your formula worked perfectly. Thank you. I'm curious,
though, why does one need to use the Abs function in the formula? Joel

Joel.
1) Beginning of year balances for units and $ investment
= Sum(Abs([Ativity date] < #01/01/2004#)*[Units])
= Sum(Abs([Ativity date] < #01/01/2004#)*[$ Investment])

2) monthly activity totals for units and $ investment
Create a subreport grouped by month and filtered for 2004 only.

3) end of year totals for the items mentioned
Use the same formula's as for 1) but change the 2005 to 2005.

Hope this helps.
Fons

I'm trying to prepare an annual activity report for each of our partner's
capital accounts. The field names are [partner name],[activity date],
[units] and [$ investment]. I have a query that pulls the partner name &
address info from one table and the activity info from another table. I
would like to be able to report 1) Beginning of year balances for units and $
investment, 2) monthly activity totals for units and $ investment and 3) end
of year totals for the items mentioned. I've tried the following expression
in the partner name page footer - iif ([activity date < #01/01/2004#, sum
([units]),0) in order to print the prior year's totals but the output is
showing totals up through today. Any help on reporting items 1 and 2 would
be appreciated.

 

[Guest]

Not so brilliant, just been monitoring this site for quite some time and have
learned a lot. About the Abs() function. [Ativity date] < #01/01/2004#
checks for a true or false condition, this would return a -1 (minus 1) or 0
(zero), The zero we can work with but the -1 we must convert to 1 in order
to use it in the rest of the formula.
Glad that it worked.
Take care.
Fons

Fons,

You're brilliant. Your formula worked perfectly. Thank you. I'm curious,
though, why does one need to use the Abs function in the formula? Joel

Joel.
1) Beginning of year balances for units and $ investment
= Sum(Abs([Ativity date] < #01/01/2004#)*[Units])
= Sum(Abs([Ativity date] < #01/01/2004#)*[$ Investment])

2) monthly activity totals for units and $ investment
Create a subreport grouped by month and filtered for 2004 only.

3) end of year totals for the items mentioned
Use the same formula's as for 1) but change the 2005 to 2005.

Hope this helps.
Fons

I'm trying to prepare an annual activity report for each of our partner's
capital accounts. The field names are [partner name],[activity date],
[units] and [$ investment]. I have a query that pulls the partner name &
address info from one table and the activity info from another table. I
would like to be able to report 1) Beginning of year balances for units and $
investment, 2) monthly activity totals for units and $ investment and 3) end
of year totals for the items mentioned. I've tried the following expression
in the partner name page footer - iif ([activity date < #01/01/2004#, sum
([units]),0) in order to print the prior year's totals but the output is
showing totals up through today. Any help on reporting items 1 and 2 would
be appreciated.