[AccessD] Need help with Eval() function

Very cool.  My formulae do consist of field names and numbers only, so
this should give me a good start in the right direction.  They do come
from different tables though, so I'll need to hammer on this for a bit
before I get it to work.

I've been working in Access97, and the Replace function appears not to
be there.  I can switch to XP with no real problem - assuming I'll find
it there, along with more details about how to use it.

Thanks for the assist!  I'll let you know how it all works out.

Don

-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg] 
Sent: Tuesday, September 09, 2003 4:03 PM
To: Access Developers discussion and problem solving
Subject: RE: [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.



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com