If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Help with Sum Function in Report
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 |
#3
|
|||
|
|||
Help with Sum Function in Report
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 |
#4
|
|||
|
|||
Help with Sum Function in Report
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 |
#5
|
|||
|
|||
Help with Sum Function in Report
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 |
#6
|
|||
|
|||
Help with Sum Function in Report
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 . |
#7
|
|||
|
|||
Help with Sum Function in Report
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 |
#8
|
|||
|
|||
Help with Sum Function in Report
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 |
#9
|
|||
|
|||
Help with Sum Function in Report
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 |
Thread Tools | |
Display Modes | |
|
|