<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 5.50.4728.2300" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff size=2>Thanks
for the followup.</FONT></SPAN></DIV>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff
size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff
size=2>Function Cost(strFormula As String,
_<BR>
lngLocRNum As Long,
_<BR>
lngProdRNum As Long,
_<BR>
lngVolRNum As Long,
_<BR>
lngMatRNum As Long) As Currency<BR>On Error GoTo
ErrorHandle<BR> Dim lngLoopCount As Long, intProcCount As
Long, strSQL(1 To 4) As String, rs As Recordset<BR> strSQL(1)
= "Select * from tblLocations where locID = " &
lngLocRNum<BR> strSQL(2) = "Select * from tblProducts where
prdID = " & lngProdRNum<BR> strSQL(3) = "Select * from
tblVolumeDetails where volID = " & lngVolRNum<BR>
strSQL(4) = "Select * from tblMaterials where matID = " &
lngMatRNum<BR> strFormula =
UCase$(strFormula)<BR> For intProcCount = 1 To
4<BR> Set rs =
CurrentDb.OpenRecordset(strSQL(intProcCount))<BR>
For lngLoopCount = 0 To rs.Fields.Count -
1<BR>
strFormula = Replace(strFormula, UCase$(rs(lngLoopCount).Name),
Nz(rs(lngLoopCount), "0"))<BR> Next
lngLoopCount<BR> Next intProcCount<BR> Cost
= Eval(strFormula)<BR>FunctionExit:<BR> Set rs =
Nothing<BR> Exit
Function<BR>ErrorHandle:<BR> Select Case
Err.Number<BR> Case
Else<BR>
MsgBox Err.Number & " " & Err.Description & vbCrLf & vbCrLf
& "Error in function Cost"<BR> End
Select<BR> Resume FunctionExit<BR>End
Function</FONT></SPAN></DIV>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff
size=2> - Don McGillivray</FONT></SPAN></DIV>
<DIV><SPAN class=537282415-11092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV></DIV>
<DIV><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
A.D.Tejpal [mailto:adtp@touchtelindia.net] <BR><B>Sent:</B> Thursday, September
11, 2003 1:39 AM<BR><B>To:</B> Access Developers discussion and problem
solving<BR><B>Subject:</B> Re: [AccessD] Need help with Eval()
function<BR><BR></DIV></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<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></BLOCKQUOTE></BODY></HTML>