Dynamic Calculated Field in Query Needed

I am creating a DB where the major focus is ease of maintainence for the end user.

I have a query that calculates the "exposure" caused by several different "hedges". Different type hedges have their exposure calculated using different formulas.

I want one query that will return all of the exposures for the different hedges. Currently I have a field called "hedge type" and one called "exposure".

I can put a nested IIF or a switch in the exposure field to use a different formula based upon the hedge type.

What I want to do is move this functionality to a table with 2 attributes (hedge type and formula) so that the end user will only have to edit this table when adding a new hedge type, not edit the query code.

I can call the formula from this table I have created, but I cannot get it to resolve, it just returns the text. Is this possible and if so, how?

Here is my example, somewhat simplified of course:

Hedge Table
HedgeID
HedgeType
ValX
ValY

Formula Table
HedgeType
Formula

Exposure Query
Field: HedgeID HedgeType ValX ValY Formula
Table: Hedge Hedge Hedge Hedge Formula

If the formula stored in the formula table for a specific hedge type were ValX-ValY, I want to resolve that value, not just display that text.

Hopefully this makes sense.
Thanks,
Graham ...

You can use Eval() to evaluate the formula.


Attached is a demo DB containing table Hedge with three fields Valx, Valy, Formula.

There is a public function getExposure() in a module:-
-----------------------------------------------
Public Function getExposure(x, y, z) As Variant
Do While InStr(z, "Valx") > 0
z = Left(z, InStr(z, "Valx") - 1) & Trim(Str(x)) & Mid(z, InStr(z, "Valx") + 4)
Loop

Do While InStr(z, "Valy") > 0
z = Left(z, InStr(z, "Valy") - 1) & Trim(Str) & Mid(z, InStr(z, "Valy") + 4)
Loop

getExposure = Eval(z)
End Function
-----------------------------------------------

and a query qryExposure:-
SELECT Valx, Valy, Formula, getExposure(Valx, Valy, Formula) AS Exposure
FROM Hedge;


When the query is run, it passes the three fields to the function. The function substitutes Valx and Valy in the formula (z) with their numeric values and returns its evaluated value to the query.

(The query is updatable, but you may need to add error handling code in the getExposure() function if you want to directly edit in the query result.)

Hope it helps.

Jon-

Great example!

Bob

Exactly was I was looking for! Nice job, Jon K!