> I am attempting to build an Access 97 database that incorporates a large
> number of rules regarding how the data in selected fields of a table are
> formatted in a report (i.e. the text color changes depending on the value
in
> the field and the application of the rules).  There are many rules and
they
> will probably need regular updating, so I would prefer to record the rules
> (in the form of logical expressions) in a table rather than hardcoding
them
> into the report.

> My problem is with reading the rule (a string in a text field in the table
> with the rules) and actually executing it in code.  I assume that I must
use
> the Eval() function, but have met with limited success.  If the string
> evaluates to an expression that does not contain any field names, Eval
works
> fine.  For example, if the value in field ConditionExp is "date()" (the
> quotation marks are not actually entered in the field, however it is a
text
> string), then using the following works:

> strCondition = tblCurrentStat.Fields("ConditionExp")
> tblCurrentStat.Edit
> tblCurrentStat.Fields![testresult] = eval(strCondition)
> tblCurrentStat.Update

> If however, the value in field ConditionExp is "[surText]" (the name of
> another field in the same table, again no quotation marks in entry) I get:
> "Run-time error 2482  ... can't find the name 'surtext' you entered in the
> expression. ..."

> I can overcome this by changing the entry to '[surtext]' (i.e. adding
single
> quotes) and also changing the third line of the above code to the
following:

> tblCurrentStat.Fields![testresult] =
> tblCurrentStat.Fields(Eval(strCondition))

> In this case, the value stored in the field surText is correctly entered
in
> the field TestResult, but this does not work for an expression that
contains
> anything other than a simple field reference.

> Unfortunately, none of my expressions are this simple.  They all involve a
> combination of field references and operators.

> Is there a single syntax that will work with complex expression?  Or am I
> barking up the wrong tree with Eval() altogether, and is there another
> approach?

> I have also tried copying the data from fields into variables and then
> running Eval on the variable, but essentially run into the same problem.

> Thanks in advance for your help - please let me know if I haven't been
clear
> enough in describing the problem.

> Erv

> I am attempting to build an Access 97 database that incorporates a large
> number of rules regarding how the data in selected fields of a table are
> formatted in a report (i.e. the text color changes depending on the value
in
> the field and the application of the rules).  There are many rules and
they
> will probably need regular updating, so I would prefer to record the rules
> (in the form of logical expressions) in a table rather than hardcoding
them
> into the report.

> My problem is with reading the rule (a string in a text field in the table
> with the rules) and actually executing it in code.  I assume that I must
use
> the Eval() function, but have met with limited success.  If the string
> evaluates to an expression that does not contain any field names, Eval
works
> fine.  For example, if the value in field ConditionExp is "date()" (the
> quotation marks are not actually entered in the field, however it is a
text
> string), then using the following works:

> strCondition = tblCurrentStat.Fields("ConditionExp")
> tblCurrentStat.Edit
> tblCurrentStat.Fields![testresult] = eval(strCondition)
> tblCurrentStat.Update

> If however, the value in field ConditionExp is "[surText]" (the name of
> another field in the same table, again no quotation marks in entry) I get:
> "Run-time error 2482  ... can't find the name 'surtext' you entered in the
> expression. ..."

> I can overcome this by changing the entry to '[surtext]' (i.e. adding
single
> quotes) and also changing the third line of the above code to the
following:

> tblCurrentStat.Fields![testresult] =
> tblCurrentStat.Fields(Eval(strCondition))

> In this case, the value stored in the field surText is correctly entered
in
> the field TestResult, but this does not work for an expression that
contains
> anything other than a simple field reference.

> Unfortunately, none of my expressions are this simple.  They all involve a
> combination of field references and operators.

> Is there a single syntax that will work with complex expression?  Or am I
> barking up the wrong tree with Eval() altogether, and is there another
> approach?

> I have also tried copying the data from fields into variables and then
> running Eval on the variable, but essentially run into the same problem.

> Thanks in advance for your help - please let me know if I haven't been
clear
> enough in describing the problem.

> Erv

> I am attempting to build an Access 97 database that incorporates a large
> number of rules regarding how the data in selected fields of a table are
> formatted in a report (i.e. the text color changes depending on the value
in
> the field and the application of the rules).  There are many rules and
they
> will probably need regular updating, so I would prefer to record the rules
> (in the form of logical expressions) in a table rather than hardcoding
them
> into the report.

> My problem is with reading the rule (a string in a text field in the table
> with the rules) and actually executing it in code.  I assume that I must
use
> the Eval() function, but have met with limited success.  If the string
> evaluates to an expression that does not contain any field names, Eval
works
> fine.  For example, if the value in field ConditionExp is "date()" (the
> quotation marks are not actually entered in the field, however it is a
text
> string), then using the following works:

> strCondition = tblCurrentStat.Fields("ConditionExp")
> tblCurrentStat.Edit
> tblCurrentStat.Fields![testresult] = eval(strCondition)
> tblCurrentStat.Update

> If however, the value in field ConditionExp is "[surText]" (the name of
> another field in the same table, again no quotation marks in entry) I get:
> "Run-time error 2482  ... can't find the name 'surtext' you entered in the
> expression. ..."

> I can overcome this by changing the entry to '[surtext]' (i.e. adding
single
> quotes) and also changing the third line of the above code to the
following:

> tblCurrentStat.Fields![testresult] =
> tblCurrentStat.Fields(Eval(strCondition))

> In this case, the value stored in the field surText is correctly entered
in
> the field TestResult, but this does not work for an expression that
contains
> anything other than a simple field reference.

> Unfortunately, none of my expressions are this simple.  They all involve a
> combination of field references and operators.

> Is there a single syntax that will work with complex expression?  Or am I
> barking up the wrong tree with Eval() altogether, and is there another
> approach?

> I have also tried copying the data from fields into variables and then
> running Eval on the variable, but essentially run into the same problem.

> Thanks in advance for your help - please let me know if I haven't been
clear
> enough in describing the problem.

> Erv

> very interesting...

> Because Eval runs in a global context you cannot use local variables in
> your expressions. You can use references to forms:
> "Forms!Employees!LastName" is a valid expression.

> You can also use public functions in the Eval-expression.
> So I wrote a module with a recordset declaration at module level and a
> public function to get the field value.
> You must use 'FLD(""UnitsInStock"")' in your expression string.
> (..or you could run a replace function to change '[whateverfield]' to
> 'FLD(""whateverfield"")'
> for all fieldnames in the expression.)

> Personally I would store SQL statements in the expression field. Because
you
> can use Eval only in VBA (not VB) and SQL is far more portable.

> Success,

> Martijn Bartlema

> 'runs in NWIND.MDB Access 97 - open Immidiate Window and type EvilEval
> '===============
> Option Compare Database
> Option Explicit

> Private rs As DAO.Recordset
> '===============
> Public Function EvilEval()
>   Dim strExpr As String

>   strExpr = "FLD(""ProductName"") & "": stock $"" & " & _
>   "FLD(""UnitPrice"") * FLD(""UnitsInStock"") & """ & _
>   " order $"" & FLD(""UnitPrice"") * FLD(""UnitsOnOrder"")"

>   Set rs = CurrentDb.OpenRecordset("select * from products")
>   With rs
>     .MoveFirst
>     Do
>       Debug.Print Eval(strExpr)
>       .MoveNext
>     Loop Until .EOF
>     .Close
>   End With
> End Function
> '===============
> Public Function FLD(strFieldName)
>   FLD = rs.Fields(strFieldName)
> End Function
> '===============

> --
> Martijn Bartlema

> > I am attempting to build an Access 97 database that incorporates a large
> > number of rules regarding how the data in selected fields of a table are
> > formatted in a report (i.e. the text color changes depending on the
value
> in
> > the field and the application of the rules).  There are many rules and
> they
> > will probably need regular updating, so I would prefer to record the
rules
> > (in the form of logical expressions) in a table rather than hardcoding
> them
> > into the report.

> > My problem is with reading the rule (a string in a text field in the
table
> > with the rules) and actually executing it in code.  I assume that I must
> use
> > the Eval() function, but have met with limited success.  If the string
> > evaluates to an expression that does not contain any field names, Eval
> works
> > fine.  For example, if the value in field ConditionExp is "date()" (the
> > quotation marks are not actually entered in the field, however it is a
> text
> > string), then using the following works:

> > strCondition = tblCurrentStat.Fields("ConditionExp")
> > tblCurrentStat.Edit
> > tblCurrentStat.Fields![testresult] = eval(strCondition)
> > tblCurrentStat.Update

> > If however, the value in field ConditionExp is "[surText]" (the name of
> > another field in the same table, again no quotation marks in entry) I
get:
> > "Run-time error 2482  ... can't find the name 'surtext' you entered in
the
> > expression. ..."

> > I can overcome this by changing the entry to '[surtext]' (i.e. adding
> single
> > quotes) and also changing the third line of the above code to the
> following:

> > tblCurrentStat.Fields![testresult] =
> > tblCurrentStat.Fields(Eval(strCondition))

> > In this case, the value stored in the field surText is correctly entered
> in
> > the field TestResult, but this does not work for an expression that
> contains
> > anything other than a simple field reference.

> > Unfortunately, none of my expressions are this simple.  They all involve
a
> > combination of field references and operators.

> > Is there a single syntax that will work with complex expression?  Or am
I
> > barking up the wrong tree with Eval() altogether, and is there another
> > approach?

> > I have also tried copying the data from fields into variables and then
> > running Eval on the variable, but essentially run into the same problem.

> > Thanks in advance for your help - please let me know if I haven't been
> clear
> > enough in describing the problem.

> > Erv