Hey guys,
i am using the following function in a query:
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 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!
i am using the following function in a query:
Code:
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:
Code:
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!