Help with Sum Function in Report

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!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"S Jackson" wrote in message
...
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

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" wrote in message
hlink.net...
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

www.pcdatasheet.com


"S Jackson" wrote in message
...
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

It gives me zeros if its control source is:

[SwitchOtherAmt] + [CSAAmt] + [JudgeAmt]

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" wrote in message
...
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" wrote in message
hlink.net...
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

www.pcdatasheet.com


"S Jackson" wrote in message
...
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

Another thought ----

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

You might want to incorporate the NZ function.


"S Jackson" wrote in message
...
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" wrote in message
hlink.net...
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

www.pcdatasheet.com


"S Jackson" wrote in message
...
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

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
"=3D[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:
"=3DSum(WholesalePrice]+[Markup])".

Jim

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

=3DFormat(Switch([DispositionType]=3D"Other",[Amt],[DispositionType]=3D"=
Withdrawn
by Petitioner",[Amt],[DispositionType]=3D"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:

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

Any thoughts?
S. Jackson


.

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" wrote in message
hlink.net...
Another thought ----

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

You might want to incorporate the NZ function.


"S Jackson" wrote in message
...
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" wrote in message
hlink.net...
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

www.pcdatasheet.com


"S Jackson" wrote in message
...
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

Looks like this:

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

Steve
PC datasheet


"S Jackson" wrote in message
...
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" wrote in message
hlink.net...
Another thought ----

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

You might want to incorporate the NZ function.


"S Jackson" wrote in message
...
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" wrote in message
hlink.net...
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

www.pcdatasheet.com


"S Jackson" wrote in message
...
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

Thanks PC (once again!) Your solution worked.

Shelly

"PC Datasheet" wrote in message
hlink.net...
Looks like this:

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

Steve
PC datasheet


"S Jackson" wrote in message
...
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" wrote in message
hlink.net...
Another thought ----

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

You might want to incorporate the NZ function.


"S Jackson" wrote in message
...
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" wrote in message
hlink.net...
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

www.pcdatasheet.com


"S Jackson" wrote in message
...
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