Solved Running sum based off another text box value

[oxicottin]

I have a report and in the detail i have a textbox (txt1) that has a true or false value.

I also have a textbox (txt2) thats value is a number.

I need to have a running sum textbox (txt3) that if the value of (txt1) is true then add the (txt2) number to my running sum textbox and if its false then sum zero.

 

[MarkK]

Put a global variable in the report. If txt1 is True, add txt2 to the global variable. Selectively display the summed global variable's value in txt3.
hth

 

[oxicottin]

If i had a hidden textbox named txtRunningSum and its source was =If([txt1] = True, "[txt2]", "0")

Then wouldn't it show the value of txt2 in txtRunningSum if txt1 was true and if false then display a zero in txtRunningSum.

Then in the footer of my group i can have a texbox with =sum(txtRunningSum)

Would that be right?

 

[Pat Hartman]

Would that be right?

That was what I was going to suggest. The only differences is that your sum cannot reference a control, it needs to reference actual data.

=Sum(Iif([txt1] = True, [txt2], 0)

You also need to clean up the IIf()'s syntax as I did above.

 

[oxicottin]

@Pat Hartman are you saying use the [field name] instead of the control name for txt1 and txt2.

 

[Pat Hartman]

Yes

 

[oxicottin]

@Pat Hartman, ok I tried the formula and its calculatiing I think all no mater if its true or false.

I used:
=Sum(IIf([IncludeInAverageDelay]=True,[SumOfNetProd],0))

Ok lets use the first group in the report, the first one (blue) txt1 is showing "False" so the blue textbox next to it should show 0 but it showes 7,727,050. The next row down shows true so the text box in blue next to it should show 54,400 which thats the textbox txt2 that im using for the numbers. Last for that group it shows false so it should show a 0 and in the "EmployeeName Footer" for the group it should show the number 54,400 BUT I didnt add anthing to get that number yet since the above isnt working yet.

In the last group there are two trues and two false and the summed number or result for that group should be 814,650

What am I doing wrong?

 

[Pat Hartman]

Is SumOfNetProduct the name of the control or the name of the table field. Can you see that 7,727,050 number anywhere in the data?

 

[oxicottin]

Is SumOfNetProduct the name of the control or the name of the table field. Can you see that 7,727,050 number anywhere in the data?

SumOfNetProduct is the name from the query field. I did fix the group issue by changing the properties to Running Sum/Over Group but still getting huge numbers. No I dont see them numbers on the report...

The IncludeInAverageDelay is a Yes/No data type does that make a differnce?

 

[Pat Hartman]

Try compacting the database. Make sure everything compiles. Then try to decompile. There may be corruption.

Start again with a new report. Just use the same data, don't waste time to make it pretty.

 

[oxicottin]

@Pat Hartman I figured it out... I took the sum off from your formula and used =IIf([IncludeInAverageDelay]=True,[SumOfNetProd],0) and made that textbox a Running Sum/Over Group and it works great. Then in the footer of the group I just used =[name of that running sum textbox] and it gave the last total for that group....

 

[Pat Hartman]

Great. Thanks for reporting back.