Get global variable value per record 1

[jkirkland]

Does anyone know how to see a global variable value per record if that global variable value changes per record?

My actual process is more complex, but in general I have three fields in my query (Account, Amount, FormulaType).

The 'Amount' field calls a custom function. That function runs one of two formulas (formula 1 or formula 2) depending on the value of account #.

I want to put '1' or '2' into the third field of the query depending on which formula was used, BUT I don't want to run the function a second time (it takes too long).

Instead, I use a global variable and set the global variable's value to either '1' or '2' each time the function is run.

The problem is that while the global variable's value changes each time the function is run, it doesn't change for each record in the query. Instead it, each record shows whatever formula number was used for the very first account.

I assume that the query doesn't see the "refreshed" global variable value after the first record.

Thanks for the advice!

 

[BoxHead]

I'm confused by "it doesn't change for each record in the query..."

It would seem that a global variable like you've described will be either 1 or 2. Whatever the value is for the first record would be the same value for all records. Since it's global, any query, form or report should show the same value for all records.

What am I missing?

"True organizational power lies in the paradigm shift from
Efficient Data Management to Effective Data Mastery."

 

[HandsOnAccess]

To make that to work,
Just pass a field value as a parameter to the function from the query.

Get the concept from this generic code format...
Say for example,
Let your Query be....

SELECT Account, GetFormula(AccountNo) As Amount, FormulaType From <<Table Name>>

Then ...

GetFormula is Custom Function which can be written as

Public Function GetFormula(xAccountNo as Long) As String
    If xAccountNo = ...  Then
        GetFormula = "Formula 1"
        GlobalFormulaType = 1
    Else
        GetFormula = "Formula 1"
        GlobalFormulaType = 2
    End If
End Function

The above function should execute for each row and you should get different values..

Just try the above format and also double check everything...

Regards,

 

[jkirkland]

The global variable isn't the same for each record because it is just holding a value representing which of two formulas was used in the function for each record.

The end result datasheet should look something like

Account Amount FormulaUsed
101 205.26 1
101 102.56 1
203 999.21 2

The function is similar to:

Function MyAmount(iAccount as Integer, Value1 as Double...)

If iAccount = 101 Then
MyAmount = Value1+Value2
MyGlobalVariable = 1
Else
MyAmount = Value3+Value4
MyGlobalVariable = 2
End if
End Function

The field 'FormulaUsed' is calling a function that is suppose to get the current value of MyGlobalVariable.

The function is actually alot more complex, but this is essentially what it is doing.

Thanks for looking at it!

 

[jkirkland]

HandsOnAccess,

Thanks for the reply. My reply after yours was to BoxHead. I am using your format to get the 'Amount' value, but I just don't know how to get the query to see the current value of the global variable, which I want to show in the third field, 'FormulaType'.

Sorry, its been difficult to get my head wrapped around the concept so I may not be explaining it well.

 

[HandsOnAccess]

Try this in query

SELECT Account, GetFormula(AccountNo) As Amount, <<Global Variable>> AS FormulaType From <<Table Name>>

Regards,

 

[BoxHead]

I'm not seeing what is 'global' about a variable that changes for each record.

Your function seems to be trying to establish two values for two variables.

Your query could use

IIf([iAccount]="101",Value1+Value2,Value3+Value4) for the Amount field

and

IIf([iAccount] = "101",1,2) for the FormulaUsed field.

HTH

"True organizational power lies in the paradigm shift from
Efficient Data Management to Effective Data Mastery."

 

[jkirkland]

The function isn't really as simple as I wrote above. The real function uses recordsets, calls other functions, compares multiple values, etc.

It currently has 7 "IF" statements that get evaluated per record. Each record takes about 1 full second to process and the query currently takes several minutes to run. This is without the third "FormulaType" field.

I can make the same function produce the formula number (1,2,3,etc) but this means running the function a second time on each record which so far doubles the processing time. That's what I'm trying to overcome.

I would like the formula to only run once but produce two results: 1. 'Amount' 2. Formula #

 

[PHV]

Public Function GetFormula(iAccount As Integer)
GetFormula = MyGlobalVariable
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886