Eval() Problem

[John Parker]

The following code in MS Access uses the Eval() Function.
I'm tearing my hair out trying to get it to work. Please
Help!


A simple Form "PanelCoster" allows Users to input
the "Length" and "Width"
' of a panel (in millimetres), then select a Material
from the "Materials" Table.
' The "Formula" column of the "Materials" Table
contains an expression such as "l*w*
' NB "Formula" varies depending upon the Record
Selected eg "l * w" or "(l*2)+(w*2)"

Private Sub SelectMaterial_AfterUpdate()
Dim l As Double, w As Double, EvalFormula As
Variant, PanelCost As Single

' Find the Record selected in SelectMaterial ComboBox:

DoCmd.ApplyFilter , "ID=Forms!PanelCoster!
SelectMaterial"
l = [Form]![Length] / 1000 ' convert "Length" to
metres and store in variable "l"
w = [Form]![Width] / 1000 ' convert "Width" to
metres and store in variable "w"

' A TextBox named "Formula" contains the value of
the "Formula" Data Field
' Evaluate the Expression contained in the "Formula"
TextBox

EvalFormula = Eval(Formula) ' Results in Run-Time
Error '2482'
' Can't find the name 'l' you
entered in the expression.
' ! But the Debug TipTex
indicates 'Formula="l * w"' !
PanelCost = EvalFormula * 18.1
' Display the result in a MessageBox

MsgBox "Panel Cost = £" & PanelCost

 

[Marshall Barton]

The following code in MS Access uses the Eval() Function.
I'm tearing my hair out trying to get it to work. Please
Help!


A simple Form "PanelCoster" allows Users to input
the "Length" and "Width"
' of a panel (in millimetres), then select a Material
from the "Materials" Table.
' The "Formula" column of the "Materials" Table
contains an expression such as "l*w*
' NB "Formula" varies depending upon the Record
Selected eg "l * w" or "(l*2)+(w*2)"

Private Sub SelectMaterial_AfterUpdate()
Dim l As Double, w As Double, EvalFormula As
Variant, PanelCost As Single

' Find the Record selected in SelectMaterial ComboBox:

DoCmd.ApplyFilter , "ID=Forms!PanelCoster!
SelectMaterial"
l = [Form]![Length] / 1000 ' convert "Length" to
metres and store in variable "l"
w = [Form]![Width] / 1000 ' convert "Width" to
metres and store in variable "w"

' A TextBox named "Formula" contains the value of
the "Formula" Data Field
' Evaluate the Expression contained in the "Formula"
TextBox

EvalFormula = Eval(Formula) ' Results in Run-Time
Error '2482'
' Can't find the name 'l' you
entered in the expression.
' ! But the Debug TipTex
indicates 'Formula="l * w"' !

The problem is that the Expression Service (which is used to
evaluate the expression) is not aware of VBA variables.
(The Expression Service is also used in the Jet SQL, Control
Source expression and Debug environments.)

In the Eval environment, the Expression Service is only able
to resolve literal constants, fully qualified form/report
controls, built-in Access functions and standard module
Public UDFs (User Defined Functions). (In the Jet SQL and
Control Source exression environments, it also recognizes
field references, while in the Debug environment, it depends
on what else is going on.)

What all that boils down to is that you could use a UDF in
your formula: GetL() * GetW() but that might get kind of
messy in your setup.

Or you can resolve the variable names before you ask Eval to
calculate the expression:

strEvalExp = Me!Formula
strEvalExp = Replace(strEvalExp, "L", CStr(L))
strEvalExp = Replace(strEvalExp, "W", CStr(W))
EvalFormula = Eval(strEvalExp)

 

[John Parker]

-----Original Message-----

The following code in MS Access uses the Eval() Function.

EvalFormula = Eval(Formula) ' Results in Run-Time
Error '2482'
' Can't find the name 'l' you
entered in the expression.
' ! But the Debug TipTex
indicates 'Formula="l * w"' !

-------------- ANSWER ----------

The problem is that the Expression Service (which is used to
evaluate the expression) is not aware of VBA variables.

Or you can resolve the variable names before you ask Eval to
calculate the expression:

strEvalExp = Me!Formula
strEvalExp = Replace(strEvalExp, "L", CStr(L))
strEvalExp = Replace(strEvalExp, "W", CStr(W))
EvalFormula = Eval(strEvalExp)
--
Marsh
MVP [MS Access]
.
--------- REPLY --------------

Many thanks Marshall for such a quick response.
Unfortunately my version of MS Access 97 does not appear
to support the function 'Replace()' - This Error Message appears:
'Compile Error (35) - Sub or Function not defined'

Any Suggestions?

John

 

[Marshall Barton]

--------- REPLY --------------

Many thanks Marshall for such a quick response.
Unfortunately my version of MS Access 97 does not appear
to support the function 'Replace()' - This Error Message
appears:
'Compile Error (35) - Sub or Function not defined'

Here's the function I use in versions prior to A2K:

Function Subst(Original As Variant, Search As String, _
Replace As String) As Variant
Dim pos As Long

Subst = Original
If IsNull(Subst) Then Exit Function
If Len(Search) > 0 Then
pos = InStr(Subst, Search)
Do Until pos = 0
Subst = Left(Subst, pos - 1) & Replace _
& Mid$(Subst, pos + Len(Search))
pos = InStr(pos + Len(Replace), Subst, Search)
Loop
End If
End Function

 

[John Parker]

Marsh wrote:

The problem is that the Expression Service (which is used

-----------------
Marsh Wrote:
Here's the function I use in versions prior to A2K ....

----------- reply from John Parker ------

I have upgraded to Access 2000 and "Hey Presto" the Eval

() Function now works as you might expect (provided the
indirect variables are REPLACED with direct variables as
per your coding.