[AccessD] Need help with Eval() function

Don,

    The function suggested by Stuart is truly remarkable for its elegance and generic applicability. Certain points needing care are reiterated below -
    Keeping in view the fact that in the present context, after carrying out all steps involving Replace() function, the string argument for Eval() function is required to finally contain only numbers or arithmetic operators, the formula string should not contain any table names. Only make sure that the field names used are unique so that there is no ambiguity as to which table it might belong to.
    
    Since your formula string uses fields from two different tables, you cannot yet jump to the final query displaying cost calculation - as the function looks at a single record source for its iterations of Replace() action. It also depends upon ID_No for identifying the record in question. It is therefore necessary to create a PreFinal Query based upon the two tables.

    In this query, the primary key for volume table should be given an alias Q_ID. This is to ensure that the coding in cost function need not be disturbed because of different names of primary keys. (Care is to be taken that the name of  PreFinal  query and the alias used for the relevant primary key always match those used in the function code).

    Query Q_PreFinal will be the source for final query, in which the Cost column will be generated by the following calculated field -
    Cost: Fn_Cost([CalcFormula],[Q_ID])

Code for Fn_Cost() so as to suit the above -

Function Fn_Cost(Formula As String,  _
                            RecordNum As Long) As Currency
    Dim loopcount As Long
    Dim rs As dao.Recordset
    
    Set rs = CurrentDb.OpenRecordset("Select  *  from "  & _
                        "Q_PreFinal " & _
                        "where Q_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
    Fn_Cost = Eval(Formula)
End Function

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Mcgillivray, Donald [ITS] 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, September 10, 2003 22:28
  Subject: RE: [AccessD] Need help with Eval() function


  Stuart, that's exactly the thing!  I modified your code sample to fit my
  situation, and it works like a charm.  

  Thanks so much for the advice!

  This list is the best!

  Don

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030911/cf9bc97f/attachment-0001.html>