Help with Sum Function in Report

  • Thread starter Thread starter S Jackson
  • Start date Start date
S

S Jackson

I have the following Switch function in an unbound control in the detail
section of a report and it works beautifully:

=Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdrawn
by Petitioner",[Amt],[DispositionType]="Modified Penalty",[ModifiedPenalty])
& [CSAAmt],"Currency")

However, now I want to total the amounts collected by the Switch function
along with two more fields: [CSAAmt] & [JudgeAmt] in the Group Footer.
This expression gives me a error telling me it is either typed in correctly
or too complicated - to assign variables:

=Sum(Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdr
awn by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty]) & [CSAAmt] & [JudgeAmt],"Currency"))

Any thoughts?
S. Jackson
 
Hi Shelly,

Add a hidden textbox named MyTotal beside your unbound control. Set its control
source to:
= [NameOfSwitchTextBox] + [CSAAmt] + [JudgeAmt]
Set its running sum property to True.

In the GroupFooter textbox, set its controlsource to:
=[MyTotal]

You should get what you want!
 
Hey PC!
Hummm . . .it didn't work. . .

I named the unbound control with the switch function: [SwitchOtherAmt]. I
added the hidden txbox named MyTotal beside it. (Except at first I didn't
hide it because I wanted to see what it did.) I set the Running Sum
property to "Over Group." It gives me zeros if its control source is:

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

I will give me a running sum if I set its control source to just one of the
above (e.g. =[SwitchOtherAmt]), but as soon as I try to add another field, I
get zeros. I tried adding the sum function and then it wanted to know where
the [SwitchOtherAmt] was. Any ideas about what I am doing wrong or missing?

Shelly

PC Datasheet said:
Hi Shelly,

Add a hidden textbox named MyTotal beside your unbound control. Set its control
source to:
= [NameOfSwitchTextBox] + [CSAAmt] + [JudgeAmt]
Set its running sum property to True.

In the GroupFooter textbox, set its controlsource to:
=[MyTotal]

You should get what you want!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


S Jackson said:
I have the following Switch function in an unbound control in the detail
section of a report and it works beautifully:

=Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdrawn
by Petitioner",[Amt],[DispositionType]="Modified Penalty",[ModifiedPenalty])
& [CSAAmt],"Currency")

However, now I want to total the amounts collected by the Switch function
along with two more fields: [CSAAmt] & [JudgeAmt] in the Group Footer.
This expression gives me a error telling me it is either typed in correctly
or too complicated - to assign variables:

=Sum(Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdr
awn by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty]) & [CSAAmt] & [JudgeAmt],"Currency"))

Any thoughts?
S. Jackson
Click to expand...
Click to expand...
 
It gives me zeros if its control source is:
[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]
Click to expand...

1. I presume you had = in front of the expression!

2. Grasping at straws ---
add a second hidden textbox and put this in the controlsource:
= [SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

set the original hidden textbox's controlsource = to this textbox.
Keep your running sum on the first textbox.

S Jackson said:
Hey PC!
Hummm . . .it didn't work. . .

I named the unbound control with the switch function: [SwitchOtherAmt]. I
added the hidden txbox named MyTotal beside it. (Except at first I didn't
hide it because I wanted to see what it did.) I set the Running Sum
property to "Over Group." It gives me zeros if its control source is:

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

I will give me a running sum if I set its control source to just one of the
above (e.g. =[SwitchOtherAmt]), but as soon as I try to add another field, I
get zeros. I tried adding the sum function and then it wanted to know where
the [SwitchOtherAmt] was. Any ideas about what I am doing wrong or missing?

Shelly

PC Datasheet said:
Hi Shelly,

Add a hidden textbox named MyTotal beside your unbound control. Set its control
source to:
= [NameOfSwitchTextBox] + [CSAAmt] + [JudgeAmt]
Set its running sum property to True.

In the GroupFooter textbox, set its controlsource to:
=[MyTotal]

You should get what you want!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


S Jackson said:
I have the following Switch function in an unbound control in the detail
section of a report and it works beautifully:

=Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdrawn
by Petitioner",[Amt],[DispositionType]="Modified Penalty",[ModifiedPenalty])
& [CSAAmt],"Currency")

However, now I want to total the amounts collected by the Switch function
along with two more fields: [CSAAmt] & [JudgeAmt] in the Group Footer.
This expression gives me a error telling me it is either typed in correctly
or too complicated - to assign variables:

=Sum(Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdr
awn by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty]) & [CSAAmt] & [JudgeAmt],"Currency"))

Any thoughts?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
Another thought ----

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]
could any of these be null?

You might want to incorporate the NZ function.


S Jackson said:
Hey PC!
Hummm . . .it didn't work. . .

I named the unbound control with the switch function: [SwitchOtherAmt]. I
added the hidden txbox named MyTotal beside it. (Except at first I didn't
hide it because I wanted to see what it did.) I set the Running Sum
property to "Over Group." It gives me zeros if its control source is:

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

I will give me a running sum if I set its control source to just one of the
above (e.g. =[SwitchOtherAmt]), but as soon as I try to add another field, I
get zeros. I tried adding the sum function and then it wanted to know where
the [SwitchOtherAmt] was. Any ideas about what I am doing wrong or missing?

Shelly

PC Datasheet said:
Hi Shelly,

Add a hidden textbox named MyTotal beside your unbound control. Set its control
source to:
= [NameOfSwitchTextBox] + [CSAAmt] + [JudgeAmt]
Set its running sum property to True.

In the GroupFooter textbox, set its controlsource to:
=[MyTotal]

You should get what you want!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


S Jackson said:
I have the following Switch function in an unbound control in the detail
section of a report and it works beautifully:

=Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdrawn
by Petitioner",[Amt],[DispositionType]="Modified Penalty",[ModifiedPenalty])
& [CSAAmt],"Currency")

However, now I want to total the amounts collected by the Switch function
along with two more fields: [CSAAmt] & [JudgeAmt] in the Group Footer.
This expression gives me a error telling me it is either typed in correctly
or too complicated - to assign variables:

=Sum(Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdr
awn by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty]) & [CSAAmt] & [JudgeAmt],"Currency"))

Any thoughts?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
I got this from Dirk Goldgar. It is very informative

Check the following points:

(1) The summing text box must be in a group or report
footer or header section. Usually it will be in a footer
section. Don't put it in a page footer or header.

(2) The name of the summing text box must not be the same
as the name of a field in the report's recordsource. So if
your text box is summing the [Retail Price] field, as with
the controlsource you posted, name it something like
"txtTotalRetail".

(3) The argument of the Sum() function must be a field or
an expression of fields in the report's recordsource; it
can't be the name of an unbound or calculated control. If
you have a calculated control, for example if [Retail
Price] is a text box with controlsource
"=[WholesalePrice]+[Markup]", where [WholesalePrice] and
[Markup] are fields in the recordsource, then you must
repeat the calculation in the argument to the Sum()
function, as with this controlsource:
"=Sum(WholesalePrice]+[Markup])".

Jim
 
Yup - some of the values both [CSAAmt] & [JudgeAmt] and [SwitchOtherAmt] are
null. I've been working on the Nz function, but I can't get it to work.
Keep getting errors :(

S. Jackson

PC Datasheet said:
Another thought ----

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]
could any of these be null?

You might want to incorporate the NZ function.


S Jackson said:
Hey PC!
Hummm . . .it didn't work. . .

I named the unbound control with the switch function: [SwitchOtherAmt]. I
added the hidden txbox named MyTotal beside it. (Except at first I didn't
hide it because I wanted to see what it did.) I set the Running Sum
property to "Over Group." It gives me zeros if its control source is:

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

I will give me a running sum if I set its control source to just one of the
above (e.g. =[SwitchOtherAmt]), but as soon as I try to add another field, I
get zeros. I tried adding the sum function and then it wanted to know where
the [SwitchOtherAmt] was. Any ideas about what I am doing wrong or missing?

Shelly

PC Datasheet said:
Hi Shelly,

Add a hidden textbox named MyTotal beside your unbound control. Set
Click to expand...
its
control
source to:
= [NameOfSwitchTextBox] + [CSAAmt] + [JudgeAmt]
Set its running sum property to True.

In the GroupFooter textbox, set its controlsource to:
=[MyTotal]

You should get what you want!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I have the following Switch function in an unbound control in the detail
section of a report and it works beautifully:
Click to expand...
=Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdrawn
by Petitioner",[Amt],[DispositionType]="Modified Penalty",[ModifiedPenalty])
& [CSAAmt],"Currency")

However, now I want to total the amounts collected by the Switch function
along with two more fields: [CSAAmt] & [JudgeAmt] in the Group Footer.
This expression gives me a error telling me it is either typed in correctly
or too complicated - to assign variables:
Click to expand...
=Sum(Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdr
awn by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty]) & [CSAAmt] & [JudgeAmt],"Currency"))

Any thoughts?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
Looks like this:

NZ( [CSAAmt],0) + NZ( [JudgeAmt],0) + NZ([SwitchOtherAmt],0)

Steve
PC datasheet


S Jackson said:
Yup - some of the values both [CSAAmt] & [JudgeAmt] and [SwitchOtherAmt] are
null. I've been working on the Nz function, but I can't get it to work.
Keep getting errors :(

S. Jackson

PC Datasheet said:
Another thought ----

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]
could any of these be null?

You might want to incorporate the NZ function.


S Jackson said:
Hey PC!
Hummm . . .it didn't work. . .

I named the unbound control with the switch function: [SwitchOtherAmt]. I
added the hidden txbox named MyTotal beside it. (Except at first I didn't
hide it because I wanted to see what it did.) I set the Running Sum
property to "Over Group." It gives me zeros if its control source is:

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

I will give me a running sum if I set its control source to just one of the
above (e.g. =[SwitchOtherAmt]), but as soon as I try to add another field, I
get zeros. I tried adding the sum function and then it wanted to know where
the [SwitchOtherAmt] was. Any ideas about what I am doing wrong or missing?

Shelly

Hi Shelly,

Add a hidden textbox named MyTotal beside your unbound control. Set its
control
source to:
= [NameOfSwitchTextBox] + [CSAAmt] + [JudgeAmt]
Set its running sum property to True.

In the GroupFooter textbox, set its controlsource to:
=[MyTotal]

You should get what you want!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I have the following Switch function in an unbound control in the detail
section of a report and it works beautifully:


=Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdrawn
by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty])
& [CSAAmt],"Currency")

However, now I want to total the amounts collected by the Switch
function
along with two more fields: [CSAAmt] & [JudgeAmt] in the Group Footer.
This expression gives me a error telling me it is either typed in
correctly
or too complicated - to assign variables:


=Sum(Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdr
awn by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty]) & [CSAAmt] & [JudgeAmt],"Currency"))

Any thoughts?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
Thanks PC (once again!) Your solution worked.

Shelly

PC Datasheet said:
Looks like this:

NZ( [CSAAmt],0) + NZ( [JudgeAmt],0) + NZ([SwitchOtherAmt],0)

Steve
PC datasheet


S Jackson said:
Yup - some of the values both [CSAAmt] & [JudgeAmt] and [SwitchOtherAmt] are
null. I've been working on the Nz function, but I can't get it to work.
Keep getting errors :(

S. Jackson

PC Datasheet said:
Another thought ----

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]
could any of these be null?

You might want to incorporate the NZ function.


Hey PC!
Hummm . . .it didn't work. . .

I named the unbound control with the switch function:
Click to expand...
[SwitchOtherAmt].
I
added the hidden txbox named MyTotal beside it. (Except at first I didn't
hide it because I wanted to see what it did.) I set the Running Sum
property to "Over Group." It gives me zeros if its control source is:

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

I will give me a running sum if I set its control source to just one
Click to expand...
of
the
above (e.g. =[SwitchOtherAmt]), but as soon as I try to add another field, I
get zeros. I tried adding the sum function and then it wanted to
Click to expand...
know
where
the [SwitchOtherAmt] was. Any ideas about what I am doing wrong or missing?

Shelly

Hi Shelly,

Add a hidden textbox named MyTotal beside your unbound control.
Click to expand...
Set
its
control
source to:
= [NameOfSwitchTextBox] + [CSAAmt] + [JudgeAmt]
Set its running sum property to True.

In the GroupFooter textbox, set its controlsource to:
=[MyTotal]

You should get what you want!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


I have the following Switch function in an unbound control in
Click to expand...
the
detail
section of a report and it works beautifully:
Click to expand...
=Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdrawn
by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty])
& [CSAAmt],"Currency")

However, now I want to total the amounts collected by the Switch
function
along with two more fields: [CSAAmt] & [JudgeAmt] in the Group Footer.
This expression gives me a error telling me it is either typed in
correctly
or too complicated - to assign variables:
Click to expand...
=Sum(Format(Switch([DispositionType]="Other",[Amt],[DispositionType]="Withdr
awn by Petitioner",[Amt],[DispositionType]="Modified
Penalty",[ModifiedPenalty]) & [CSAAmt] & [JudgeAmt],"Currency"))

Any thoughts?
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
Back
Top