# Thread: Passing a Static Variable - Access 07

1. Registered User
Join Date
Jan 2009
Posts
8

## Unanswered: Passing a Static Variable - Access 07

I am attempting to create a static variable for accumulating a Currency value for comparison and summation in other functions. Basically I need to set a static variable = to the result of the Start Month. Then, on the SecondMonth, take the total potential, subtract the static variable and perform an If Statement based on which value is greater. This repeats for 9 months (3 shown). My static variable is not being stored/passed for future functions to use.

Note: I am calling each function in a seperate column for each month in a query.

Code:
```Static Function StartMonth(iMonth As Date, iBegin As Date, iWeeks As Integer, iPotential As Currency)
Static Accumulate As Currency
Dim MaxMonth As Date
Dim Max As Date
Dim Min As Date
Dim MinTemp As Date

If iBegin >= MaxMonth Then
Max = iBegin
Else
Max = MaxMonth
End If

MinTemp = iBegin + 7 * iWeeks + 1

If MinTemp < iMonth Then
Min = MinTemp
Else
Min = iMonth
End If

StartMonth = DateDiff("d", Max, Min)

If StartMonth > 0 Then
StartMonth = (StartMonth * iPotential) / (iWeeks * 7)
Else
StartMonth = 0
End If

If StartMonth > iPotential Then
StartMonth = iPotential
Else
End If

Accumulate = StartMonth

End Function

Function SecondMonth(iMonth As Date, iBegin As Date, iWeeks As Integer, iPotential As Currency)

Dim MinMonth As Date
Dim Max As Date
Dim Min As Date
Dim MinTemp As Date

If iWeeks <> "0" Then

If iBegin >= iMonth Then
Max = iBegin
Else
Max = iMonth
End If

MinTemp = iBegin + 7 * iWeeks + 1

If MinTemp < MinMonth Then
Min = MinTemp
Else
Min = MinMonth
End If

SecondMonth = DateDiff("d", Max, Min)

If SecondMonth >= 0 Then
SecondMonth = SecondMonth
Else
SecondMonth = 0
End If

If SecondMonth = 0 Then
SecondMonth = "0"
Else
SecondMonth = (SecondMonth * iPotential) / (iWeeks * 7)
End If
Else
SecondMonth = "0"
End If

If SecondMonth > (iPotential - Accumulate) Then
SecondMonth = (iPotential - Accumulate)
Else
End If
Accumulate = Accumulate + SecondMonth

End Function

Function ThirdMonth(iMonth As Date, iBegin As Date, iWeeks As Integer, iPotential As Currency)
Dim MaxMonth As Date
Dim MinMonth As Date
Dim Max As Date
Dim Min As Date
Dim MinTemp As Date

If iWeeks <> "0" Then

If iBegin >= MaxMonth Then
Max = iBegin
Else
Max = MaxMonth
End If

MinTemp = iBegin + 7 * iWeeks + 1

If MinTemp < MinMonth Then
Min = MinTemp
Else
Min = MinMonth
End If

ThirdMonth = DateDiff("d", Max, Min)

If ThirdMonth >= 0 Then
ThirdMonth = ThirdMonth
Else
ThirdMonth = 0
End If

If ThirdMonth = 0 Then
ThirdMonth = "0"
Else
ThirdMonth = (ThirdMonth * iPotential) / (iWeeks * 7)
End If
Else
ThirdMonth = "0"
End If

If ThirdMonth > (iPotential - Accumulate) Then
ThirdMonth = (iPotential - Accumulate)
Else
End If
Accumulate = Accumulate + ThirdMonth

End Function```

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
My static variable is not being stored/passed for future functions to use.
I take it this is the problem?
Static variables are scoped for the procedure only - you want to use a module level variable. Probably a private member variable in a class. I haven't looked at your code well enough but it looks like there is a LOT of scope for putting common code into a single function.

3. Registered User
Join Date
Jan 2009
Posts
8
I understand what you mean about Static, I misinterpreted the description in the help file. You are correct with the problem that in SecondMonth and ThirdMonth, "Accumulate" is not being passed from StartMonth for the If Statement at the bottom of each function.

I don't follow exactly what you mean here:
Probably a private member variable in a class. I haven't looked at your code well enough but it looks like there is a LOT of scope for putting common code into a single function.
Something else that occurred to me is that, StartMonth's values are being shown in the query, along with every other month. Instead of creating a variable, is there a way in SecondMonth to call or retrieve the value returned from StartMonth from the query? And for ThirdMonth to call or retrieve the value from SecondMonth from the query? And so on for all months?

4. Registered User
Join Date
Jan 2009
Posts
8
Any other thoughts on this?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•