Running Total Function acting Funky

[poet1234]

I found this code from a post several months ago:

QUERY defined new column named CumTot passing Fcast as parameter:
CumTot: CalcCumTot([Fcast])

FUNCTION in a module assumes long value in and out:
function CalcCumTot(iValue as long) as Long
Static new CumTotal as long
CumTotal = CumTotal + iValue
CalcCumTotal = CumTotal
end function

I applied it to my query and it works great. However, each time I run the query, all of the numbers in the Fcast column are greater than the previous time I ran the query. Only when I quit Access completely then come back in and run the query does it reset.

If only I could figure out how to solve this...please help. Thanks in advance.

 

[FoFa]

Static new CumTotal as long

Static means create a variable init to zero unless it exists, if it exists use what ever it contained before.

So you need to way to tell the static variable to reinitialize

 

[poet1234]

FoFa, thank you for the suggestion. I found an Access help file that explained the "lifetime" of a variable, but it was unclear to me how to actually reset the variable. I tried several things. First I tried setting CumTotal = 0 at the end of the function. I also tried it at the start of the function. Neither worked, as they simply displayed the exact value that is in the iValue variable. Could you point me in the right direction? Thanks again.

 

[pbaldy]

You need to set CumTotal = 0 before the query runs, so that in can accumulate while the query runs. If the query is used for a report, then perhaps in the code right before opening the report.

CumTotal = 0
DoCmd.OpenReport ...

 

[FoFa]

Isn't that variable local to the function (Can't be seen or set outside the function)?
You could use a variable defined at the module level (instead of inside the function), that would allow you to init the variable to zero, it would not need to be static then, but would not work if this is a shared application.
You could have the function accept a second parameter, say a boolean called it reset flag, if true init the static variable to zero, if false leave it as is. Then before you run your query you can issue a single call to the function to reinit the function, and use FALSE elsewhere.
Just some thoughts.

 

[poet1234]

FoFa, you are correct in that I was unable to use the variable outside of the function. I will spend some time this morning and over the weekend applying your latest suggestions, but may still need help with it. I think I am 95% there -- if I can solve this problem I will have a merry Christmas indeed!

 

[poet1234]

I'm still struggling with this. I don't understand how to set up this particular function with an optional argument. Actually, I do know how, I'm just unclear on how to pass the argument. Here is what I am ultimately trying to acheive. I have the function populating a cumulative total field in my query (a make table query). I have a button on a form that runs the make table query, then graphs the results and displays the graph in a report. I need the static variable reset. The form where the button is displays a list of "Accounts." The query is run based upon the account number. Thus, I only have one query and one report, but without setting the static variable to zero, all of the graphs are skewed. Please help, I'm going nuts. Thanks again.

 

[pbaldy]

Take this line out of the function:

Static new CumTotal as long

Add this to a public module:

Public CumTotal As Long

Then make the first line behind the button that runs the make table query:

CumTotal = 0

 

[poet1234]

Paul, your suggestion worked! Life is good! I am so very grateful...