formulas stored in table - help

[LSH]

Is it possible to store formulas in a table based on site
IDs and then pull the formulas into a query?

For example if I have a table called tblTaxes with fields
lngSiteID - long
dblFedTax - double
dblSTTax - double
dblCountyTax - double
dblLocalTax - double
dlbMiscTax - double
dblSalesPer - double
txtFormula - text

another table
tblSales
lngSiteID - long
dblTotSales - double


There are different formulas to calculate the total tax. A
formula could be ([dblTotSales]+ [dblFedTax]) *
[dblSalesPer]) + [dblSTTax] or it could be (([dblTotSales]
+ [dblFedTax] + [dblSTTax])* [dblSalesPer]). This is a
simplified example and there are many different formulas.
I could do this through code or a nested iif statement,
but was wanting to be able to store the formulas in a
table so that a user could modify them or add as needed. I
tried storing the formula in the [txtformula] field
as "([dblTotSales]+ [dblFedTax]) * [dblSalesPer]) +
[dblSTTax]" (no actual quotes in the field)

Playing around with it, if I pull the formula out of the
table in a query by joining the above tables by lngSiteID,
it just displays as a text string. Is there anyway to tell
Access this is a formula? I tried the Eval function, but
could not get it to work.

Any suggestions on how to make this work or am I way off
base here ... is there a better way to do this.

thanks
Laura

 

[Michel Walsh]

Hi,



Eval would have been the way to go:

SELECT psiP1(p1) AS Expr2,
psiP2(p2) AS Expr3,
formula,
Eval(formula) AS Expr1
FROM Formulae;



with the psi-functions:

===========================
Public Function psiP1(Optional p1 As Variant) As Long
Static psi As Long
If Not IsMissing(p1) Then psi = p1
psiP1 = psi
End Function


Public Function psiP2(Optional P2 As Variant) As Long
Static psi As Long
If Not IsMissing(P2) Then psi = P2
psiP2 = psi
End Function
=============================

Note that the Variant is REQUIRED (but the functions don't handle null).

The first two call to the psi functions are just to initialize their static
value.

The formula use the psi functions, without argument.


Query45

Expr2 Expr3 formula Expr1
1 2 psiP1( ) + psiP2( ) 3
3 4 psiP1( ) * psiP2( ) 12



The formula CANNOT BE a constant, ie, no:


SELECT psiP1(p1) AS Expr2,
psiP2(p2) AS Expr3,
formula,
Eval("psiP1()+psiP2()") AS Expr1 ' <<<< no good
FROM Formulae;


because, then, it becomes a constant evaluated once, at the begining of the
query, and the result is just copied for all the row of the result.


Hoping it may help,
Vanderghast, Access MVP

 

[LSH]

thank you very much for the reply.

I'm not sure that I follow you, but I will play around
with this and see if the light bulb comes on.

thanks again
Laura

-----Original Message-----
Hi,



Eval would have been the way to go:

SELECT psiP1(p1) AS Expr2,
psiP2(p2) AS Expr3,
formula,
Eval(formula) AS Expr1
FROM Formulae;



with the psi-functions:

===========================
Public Function psiP1(Optional p1 As Variant) As Long
Static psi As Long
If Not IsMissing(p1) Then psi = p1
psiP1 = psi
End Function


Public Function psiP2(Optional P2 As Variant) As Long
Static psi As Long
If Not IsMissing(P2) Then psi = P2
psiP2 = psi
End Function
=============================

Note that the Variant is REQUIRED (but the functions don't handle null).

The first two call to the psi functions are just to initialize their static
value.

The formula use the psi functions, without argument.


Query45

Expr2 Expr3 formula Expr1
1 2 psiP1( ) + psiP2( ) 3
3 4 psiP1( ) * psiP2( ) 12



The formula CANNOT BE a constant, ie, no:


SELECT psiP1(p1) AS Expr2,
psiP2(p2) AS Expr3,
formula,
Eval("psiP1()+psiP2()") AS Expr1 ' <<<< no good
FROM Formulae;


because, then, it becomes a constant evaluated once, at the begining of the
query, and the result is just copied for all the row of the result.


Hoping it may help,
Vanderghast, Access MVP

Is it possible to store formulas in a table based on site
IDs and then pull the formulas into a query?

For example if I have a table called tblTaxes with fields
lngSiteID - long
dblFedTax - double
dblSTTax - double
dblCountyTax - double
dblLocalTax - double
dlbMiscTax - double
dblSalesPer - double
txtFormula - text

another table
tblSales
lngSiteID - long
dblTotSales - double


There are different formulas to calculate the total tax. A
formula could be ([dblTotSales]+ [dblFedTax]) *
[dblSalesPer]) + [dblSTTax] or it could be (([dblTotSales]
+ [dblFedTax] + [dblSTTax])* [dblSalesPer]). This is a
simplified example and there are many different formulas.
I could do this through code or a nested iif statement,
but was wanting to be able to store the formulas in a
table so that a user could modify them or add as needed. I
tried storing the formula in the [txtformula] field
as "([dblTotSales]+ [dblFedTax]) * [dblSalesPer]) +
[dblSTTax]" (no actual quotes in the field)

Playing around with it, if I pull the formula out of the
table in a query by joining the above tables by lngSiteID,
it just displays as a text string. Is there anyway to tell
Access this is a formula? I tried the Eval function, but
could not get it to work.

Any suggestions on how to make this work or am I way off
base here ... is there a better way to do this.

thanks
Laura

.