Results 1 to 4 of 4
  1. #1
    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
    MaxMonth = DateAdd("m", -1, iMonth)
    
    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
    MinMonth = DateAdd("m", 1, iMonth)
    
    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
    MaxMonth = DateAdd("m", 1, iMonth)
    MinMonth = DateAdd("m", 2, iMonth)
    
    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. #2
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    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. #4
    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
  •