Calculations

[chickalina]

I have a report that is working fine except for one thing.... I want
subtotals based on a field. On the form this is a drop down box with one of
three choices. I need the totals based on each of these three choices, BUT,
since the report is by country I need the totals in the Country Footer (I
think)

Country 2005 2006 2007 2008
Local
Item1
Item2
Item3
Local total
NotLocal
Item4
Item5
Item6
NotLocal total

(this is the problem part, based on all records, the following should total)
TotalCredit
TotalCash
TotalCredit and Cash

GRAND TOTAL

I've tried the following (in the Country Footer)

=Val(nz([SumYear1],([benefittype]="cash only")))
=Sum(nz([benefittype]="etr only"))
and some others....

Help, please...
Thanks.

 

[Duane Hookom]

It would help if you posted the significant fields from the report's record
source. If you want to total FieldA in a report where the value of FieldB
="Red" then you can use an expression like:
=Sum(Abs(FieldB="Red") * FieldA)

 

[chickalina]

If I use the known columns/fields, then I'd have to create a report for every
5 year period... that's a lot of reports.

So, what would the code look like? I'm trying this:

txt_ETR1.ControlSource = (Forms![frm_CustomReports]![txtYear]) +
(BenefitType = "ETR Only") + 0

it works for the first line item, but not the second or third.

How would you have set this up differently?

I don't care for solutions that require setting control source properties in
code. You would need your code to set the control source of the sum text box
much like you set the control source of your other text box.

I much prefer creating a report record source with always known
columns/fields.

--
Duane Hookom
Microsoft Access MVP

Thanks Duane,
I tried what you wrote.... it works fine if you put the actual field name
from the field list (2006, which is a Query Field from a Select Query). It
does not work choosing the value from the unbound text box (using the text
box name of Year1). The unbound text box uses

Year1.ControlSource = Forms![frm_CustomReports]![txtYear] + 0

in the On Open Event of the report so that the correct values are placed in
Year1, Year2, etc... from the user's entry in the form used to open this
report.

I hope this makes sense.
M

It would help if you posted the significant fields from the report's record
source. If you want to total FieldA in a report where the value of FieldB
="Red" then you can use an expression like:
=Sum(Abs(FieldB="Red") * FieldA)

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is working fine except for one thing.... I want
subtotals based on a field. On the form this is a drop down box with one of
three choices. I need the totals based on each of these three choices, BUT,
since the report is by country I need the totals in the Country Footer (I
think)

Country 2005 2006 2007 2008
Local
Item1
Item2
Item3
Local total
NotLocal
Item4
Item5
Item6
NotLocal total

(this is the problem part, based on all records, the following should total)
TotalCredit
TotalCash
TotalCredit and Cash

GRAND TOTAL

I've tried the following (in the Country Footer)

=Val(nz([SumYear1],([benefittype]="cash only")))
=Sum(nz([benefittype]="etr only"))
and some others....

Help, please...
Thanks.

 

[chickalina]

..Duane,
I think that your instructions only work for year if the numbers are stored
by year (quarter end date: i.e. 1st Qtr=3/31/2007). My numbers are stored by
quarter, which is why I chose to do the Select Query. I used the ColHead
expression and Access changes it from:

ColHead: "Year" & DateDiff("yy",[qtrenddate],Forms!frm_CustomReports!txtyear)
to
ColHead: "Year" &
DateDiff("yy",[qtrenddate],[Forms]![frm_CustomReports]![txtyear])

Thanks.

I thought I pointed you to a better solution back in November.
http://groups.google.com/group/micr...0eb41?lnk=st&q=&rnum=1&hl=en#d6a1178cc4b0eb41

You never replied back in that thread (at least that I could find). Here is
the link that I sent to you that involves months rather than years but the
concept is exactly the same. http://www.tek-tips.com/faqs.cfm?fid=5466


--
Duane Hookom
Microsoft Access MVP

If I use the known columns/fields, then I'd have to create a report for every
5 year period... that's a lot of reports.

So, what would the code look like? I'm trying this:

txt_ETR1.ControlSource = (Forms![frm_CustomReports]![txtYear]) +
(BenefitType = "ETR Only") + 0

it works for the first line item, but not the second or third.

How would you have set this up differently?

I don't care for solutions that require setting control source properties in
code. You would need your code to set the control source of the sum text box
much like you set the control source of your other text box.

I much prefer creating a report record source with always known
columns/fields.

--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I tried what you wrote.... it works fine if you put the actual field name
from the field list (2006, which is a Query Field from a Select Query). It
does not work choosing the value from the unbound text box (using the text
box name of Year1). The unbound text box uses

Year1.ControlSource = Forms![frm_CustomReports]![txtYear] + 0

in the On Open Event of the report so that the correct values are placed in
Year1, Year2, etc... from the user's entry in the form used to open this
report.

I hope this makes sense.
M

:

It would help if you posted the significant fields from the report's record
source. If you want to total FieldA in a report where the value of FieldB
="Red" then you can use an expression like:
=Sum(Abs(FieldB="Red") * FieldA)

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is working fine except for one thing.... I want
subtotals based on a field. On the form this is a drop down box with one of
three choices. I need the totals based on each of these three choices, BUT,
since the report is by country I need the totals in the Country Footer (I
think)

Country 2005 2006 2007 2008
Local
Item1
Item2
Item3
Local total
NotLocal
Item4
Item5
Item6
NotLocal total

(this is the problem part, based on all records, the following should total)
TotalCredit
TotalCash
TotalCredit and Cash

GRAND TOTAL

I've tried the following (in the Country Footer)

=Val(nz([SumYear1],([benefittype]="cash only")))
=Sum(nz([benefittype]="etr only"))
and some others....

Help, please...
Thanks.

 

[Duane Hookom]

Your posts have all been about years so why are you concerned about quarters?
Don't you want to just create one column per year based on a window of 5
years? That is exactly what I was proposing. Also, the DateDiff() function
should be "yyyy" not "yy". txtyear should be a date field. If it has a value
like 2006 and the text box is the beginning year then use:
ColHead: "Year" & Year([qtrenddate]) -Forms!frm_CustomReports!txtyear
--
Duane Hookom
Microsoft Access MVP

.Duane,
I think that your instructions only work for year if the numbers are stored
by year (quarter end date: i.e. 1st Qtr=3/31/2007). My numbers are stored by
quarter, which is why I chose to do the Select Query. I used the ColHead
expression and Access changes it from:

ColHead: "Year" & DateDiff("yy",[qtrenddate],Forms!frm_CustomReports!txtyear)
to
ColHead: "Year" &
DateDiff("yy",[qtrenddate],[Forms]![frm_CustomReports]![txtyear])

Thanks.

I thought I pointed you to a better solution back in November.
http://groups.google.com/group/micr...0eb41?lnk=st&q=&rnum=1&hl=en#d6a1178cc4b0eb41

You never replied back in that thread (at least that I could find). Here is
the link that I sent to you that involves months rather than years but the
concept is exactly the same. http://www.tek-tips.com/faqs.cfm?fid=5466


--
Duane Hookom
Microsoft Access MVP

If I use the known columns/fields, then I'd have to create a report for every
5 year period... that's a lot of reports.

So, what would the code look like? I'm trying this:

txt_ETR1.ControlSource = (Forms![frm_CustomReports]![txtYear]) +
(BenefitType = "ETR Only") + 0

it works for the first line item, but not the second or third.

How would you have set this up differently?


:

I don't care for solutions that require setting control source properties in
code. You would need your code to set the control source of the sum text box
much like you set the control source of your other text box.

I much prefer creating a report record source with always known
columns/fields.

--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,
I tried what you wrote.... it works fine if you put the actual field name
from the field list (2006, which is a Query Field from a Select Query). It
does not work choosing the value from the unbound text box (using the text
box name of Year1). The unbound text box uses

Year1.ControlSource = Forms![frm_CustomReports]![txtYear] + 0

in the On Open Event of the report so that the correct values are placed in
Year1, Year2, etc... from the user's entry in the form used to open this
report.

I hope this makes sense.
M

:

It would help if you posted the significant fields from the report's record
source. If you want to total FieldA in a report where the value of FieldB
="Red" then you can use an expression like:
=Sum(Abs(FieldB="Red") * FieldA)

--
Duane Hookom
Microsoft Access MVP


:

I have a report that is working fine except for one thing.... I want
subtotals based on a field. On the form this is a drop down box with one of
three choices. I need the totals based on each of these three choices, BUT,
since the report is by country I need the totals in the Country Footer (I
think)

Country 2005 2006 2007 2008
Local
Item1
Item2
Item3
Local total
NotLocal
Item4
Item5
Item6
NotLocal total

(this is the problem part, based on all records, the following should total)
TotalCredit
TotalCash
TotalCredit and Cash

GRAND TOTAL

I've tried the following (in the Country Footer)

=Val(nz([SumYear1],([benefittype]="cash only")))
=Sum(nz([benefittype]="etr only"))
and some others....

Help, please...
Thanks.