[AccessD] Need help with Eval() function

Thanks for the followup.
 
Actually, my situation involves four separate tables, all of which are
included in the base query from which this function will be called.  I
revised Stuart's function as shown below to accept as arguments the
formula string and the primary keys from each table.  The field names
referred to in the formula string are unique.  The function iterates
through each of the tables, replacing any of the field names found with
their values.  Although this undermines the generic applicability of the
function, it serves my purposes well, and delivers the required result
with a minimum of effort.  In this application, I don't anticipate
needing the function for other purposes, and, in any case, it is easily
adapted if necessary.
 
Function Cost(strFormula As String, _
                lngLocRNum As Long, _
                lngProdRNum As Long, _
                lngVolRNum As Long, _
                lngMatRNum As Long) As Currency
On Error GoTo ErrorHandle
    Dim lngLoopCount As Long, intProcCount As Long, strSQL(1 To 4) As
String, rs As Recordset
    strSQL(1) = "Select * from tblLocations where locID = " & lngLocRNum
    strSQL(2) = "Select * from tblProducts where prdID = " & lngProdRNum
    strSQL(3) = "Select * from tblVolumeDetails where volID = " &
lngVolRNum
    strSQL(4) = "Select * from tblMaterials where matID = " & lngMatRNum
    strFormula = UCase$(strFormula)
    For intProcCount = 1 To 4
        Set rs = CurrentDb.OpenRecordset(strSQL(intProcCount))
        For lngLoopCount = 0 To rs.Fields.Count - 1
            strFormula = Replace(strFormula,
UCase$(rs(lngLoopCount).Name), Nz(rs(lngLoopCount), "0"))
        Next lngLoopCount
    Next intProcCount
    Cost = Eval(strFormula)
FunctionExit:
    Set rs = Nothing
    Exit Function
ErrorHandle:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & " " & Err.Description & vbCrLf & vbCrLf
& "Error in function Cost"
    End Select
    Resume FunctionExit
End Function
 
 - Don McGillivray
 
-----Original Message-----
From: A.D.Tejpal [mailto:adtp at touchtelindia.net] 
Sent: Thursday, September 11, 2003 1:39 AM
To: Access Developers discussion and problem solving
Subject: Re: [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  <mailto:donald.a.Mcgillivray at mail.sprint.com>
[ITS] 
To: Access Developers discussion and
<mailto:accessd at databaseadvisors.com> 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/c5daf15b/attachment-0001.html>