2nd run of a function caluclates wrong results (1 Viewer)

[Arvidsson]

Hey guys,
i am using the following function in a query:

Public Function Progress(ByVal varCompany As Variant _
                    , ByVal varPercent As Variant) As Variant
    Static varCompanyOld As Variant
    Static varValueOld As Variant
    Dim Value As Variant
      
    If varCompany & vbNullChar = varCompanyOld Then
        
            Value = (1 + varPercent) * varValueOld
            varValueOld = Value 
    Else
            Value = (1 + varPercent) * 100
            varValueOld = Value
            varCompanyOld = varCompany & vbNullChar
    End If   

    Progress = Value
  
End Function

This function is working if I use it with two or more companies.
If I have only one company, it works at the first (query) run, too.
But with the same company, a second (query) run calculates different results.

I think it is because of this condition:

 If varCompany & vbNullChar = varCompanyOld Then ...

If the company name is e.g. Microsoft

At the first run of the query:
Microsoft & vbNullChar = "" -> wrong
The Else calculation will be carried out.
-> That is right for the first datapoint of a company

At a second run of the query:
varCompanyOld is saved as Microsoft & vbNullChar, so:
Microsoft & vbNullChar = Microsoft & vbNullChar -> True
The main calculation will be carried out.
-> That is wrong for the first datapoint of a company

Does someone has an idea for a solution?

Thank you very much in advance!

 

[vbaInet]

Are you sure you want vbNullChar or vbNullString? Two different things.

 

[Arvidsson]

I dont know what is more suitable in this situation.

I found a code that uses vbNullChar and so I build my code with vbNullChar, too.

 

[vbaInet]

And do you know Static does?

 

[Arvidsson]

Yes. And I think I need the static variables, because the function will be called for each datapoint and there I need the values of the last datpoint.

Only at the end of the whole query, the static variables should be cleared.
I didnt find a way how I can do this.

 

[vbaInet]

Put a breakpoint and Watch the static values aren't going out of scope. And change the vbNullChar to vbNullString.

 

[Arvidsson]

I have changed in:

Public Function Progress(ByVal varCompany As Variant _
                    , ByVal varPercent As Variant) As Variant
    Dim varCompanyOld As Variant
    Dim varValueOld As Variant
    Dim Value As Variant
      
    If varCompany & vbNullString = varCompanyOld Then
...
    varCompanyOld = varCompany & vbNullString
...

Unfortunately the problem is not solved.

At the first run I get the right results, than the results will be higher and higher.

 

[Arvidsson]

If I compact/compress the database, than I get again the right results for one time.

But I think this isnt a good solution

 

[vbaInet]

Static is a bit of loose cannon. I would only use it in a class so you can reset it when instantiated.

 

[Arvidsson]

Hmmm...

I defined varCompanyOld as public variable and cleared it with an extra sub.

Option Compare Database
Public varFirmennameAlt As Variant
.....
Public Sub Variable_clear()
varCompanyOld = ""
End Sub

But it is no solution, too.

And if I click into a field in the query, than appears a other figure. And that every time if i click into.

 

[vbaInet]

A Class is different. You destroy the variable in the Destructor and initialise it in the Constructor.

Where's this code being called anyway? From a query, a textbox, an event?

 

[Arvidsson]

The code will be called from a query.

 

[vbaInet]

The query is the problem.

A query doesn't always run once when a function is called. There's a certain rule about how many times a query runs when there's a function call in it as well but I can't remember. And there's also something to do with how many function calls determines whether the query runs once or not.

Call it in a textbox's Control Source and you should be fine. Of course, requerying the form will also yield new fresh results.

 

[Arvidsson]

I found a solution:

i add:

, Optional ByVal blnReset As Boolean = False

If blnReset Then
        varFirmennameAlt = Null
        varWertAlt = Null
        Exit Function
    
    End If

Sub VariableClear()

Progress Null, Null, True

End Sub

Thank you one more time for your support!

 

[vbaInet]

Double and triple check.

Enjoy!