<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2726.2500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV>
<DIV>
<DIV><STRONG>Don,</STRONG></DIV>
<DIV> </DIV>
<DIV> The function suggested by Stuart is truly
remarkable for its elegance and generic applicability. Certain points
needing care are reiterated below -</DIV>
<DIV> 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.</DIV>
<DIV> </DIV>
<DIV> 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.</DIV>
<DIV> </DIV>
<DIV> 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).</DIV>
<DIV> </DIV>
<DIV> Query Q_PreFinal will be the source for final
query, in which the Cost column will be generated by the following
calculated field -</DIV>
<DIV> Cost: Fn_Cost([CalcFormula],[Q_ID])</DIV>
<DIV> </DIV>
<DIV><EM><U><STRONG>Code for Fn_Cost() so as to suit the
above -</STRONG></U></EM></DIV>
<DIV><EM><U></U></EM> </DIV>
<DIV>Function Fn_Cost(Formula As String, _</DIV>
<DIV>
RecordNum As Long) As
Currency<BR> Dim loopcount As Long<BR> Dim
rs As dao.Recordset<BR> <BR> Set rs =
CurrentDb.OpenRecordset("Select * from " & _</DIV>
<DIV>
"Q_PreFinal " &
_<BR>
"where Q_ID = " & RecordNum)<BR> Formula =
UCase$(Formula)<BR> For loopcount = 0 To rs.Fields.Count -
1<BR> Formula = Replace(Formula,
_</DIV>
<DIV>
UCase$(rs(loopcount).Name),
_<BR>
Nz(rs(loopcount), "0"))<BR> Next<BR> Fn_Cost
= Eval(Formula)<BR>End Function</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV><STRONG>A.D.Tejpal</STRONG></DIV>
<DIV><STRONG>--------------</STRONG></DIV></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=donald.a.Mcgillivray@mail.sprint.com
href="mailto:donald.a.Mcgillivray@mail.sprint.com">Mcgillivray, Donald
[ITS]</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">Access Developers discussion and
problem solving</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, September 10, 2003
22:28</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] Need help with
Eval() function</DIV>
<DIV><BR></DIV>Stuart, that's exactly the thing! I modified your code
sample to fit my<BR>situation, and it works like a charm. <BR><BR>Thanks
so much for the advice!<BR><BR>This list is the
best!<BR><BR>Don<BR></BLOCKQUOTE></DIV></BODY></HTML>