If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formulas stored in table - help
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 |
#2
|
|||
|
|||
formulas stored in table - help
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" wrote in message ... 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 |
#3
|
|||
|
|||
formulas stored in table - help
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 "LSH" wrote in message ... 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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Image Control Table | John Gavin | General Discussion | 3 | June 28th, 2004 04:21 AM |
What I would like to do is create a report from data stored with in a table (tbl_consultations) with the following criteria:- | Barry McConomy | General Discussion | 1 | June 7th, 2004 11:55 PM |
Cannot join 1:M table into M:M tables | Tom | Database Design | 4 | May 19th, 2004 10:16 PM |
Table design for a booking system | Brian C | Database Design | 2 | April 27th, 2004 03:11 AM |
Pivot table formulas | Alan | Worksheet Functions | 5 | October 7th, 2003 01:26 AM |