[AccessD] Need help with Eval() function

On 9 Sep 2003 at 17:12, Mcgillivray, Donald [ITS] wrote:
> I have a table of material types that may be added to over time.  I want
> the cost calculation to be dynamic, such that when a new material type
> is added, the user can define the formula that will deliver its cost.  I
> intended to build a wizard-type form that would guide the user through
> the creation of the formula by allowing the selection of fields and
> operators.  I wanted to store the formula string in the material types
> table and use it to drive the cost calculation when called.  My main
> objective is to avoid having to open up some custom function to insert
> new rules if a material type is added that doesn't fit an existing cost
> formula; I want this to be hands-off for me once I turn it over.
> 
> Am I dreaming? Or is there another smarter way to do this?
> 

If your formula consists of just fieldnames, numbers and operators, 
Eval() will still do it. But you will  need to use it inside a 
function so that you can substitue the values for the fieldnames in 
the string before you evaluate it Here's a simple function I've just 
knocked up to  do it. I an substituting  0 for Nulls. You may need to 
handle Nulls differently depending on how your formulae work.  

Also it will get slightly more complex if some of the values are 
stored in other tables - not that much harder, you just need to grab 
the fieldnames and values in a similar fashion from other tables.

Function Cost(Formula As String, RecordNum As Long) As Currency
    Dim loopcount As Long
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Select * from TestTable where ID = " & RecordNum)
    Formula = UCase$(Formula)
    For loopcount = 0 To rs.Fields.Count - 1
        Formula = Replace(Formula, UCase$(rs(loopcount).Name), NZ(rs(loopcount),"0")
    Next
    Cost = Eval(Formula)
End Function
 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.