Eval() Problem

Discussion in 'Microsoft Access Form Coding' started by John Parker, Mar 14, 2004.

1. John ParkerGuest

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

John Parker, Mar 14, 2004

2. Marshall BartonGuest

John Parker wrote:

>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)
--
Marsh
MVP [MS Access]

Marshall Barton, Mar 14, 2004

3. John ParkerGuest

>-----Original Message-----
>John Parker wrote:
>
>>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"' !

>
>
>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]
>.
>
>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

John Parker, Mar 14, 2004
4. Marshall BartonGuest

John Parker wrote:

>
>>-----Original Message-----
>>John Parker wrote:
>>
>>>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"' !

>>
>>
>>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)
>>.

>
>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

--
Marsh
MVP [MS Access]

Marshall Barton, Mar 15, 2004
5. John ParkerGuest

Re: Eval() Problem Solved

>>>-----Original Message-----
>>>John Parker wrote:
>>>
>>>> EvalFormula = Eval(Formula) ' Results in Run-

Time
>>>>Error '2482'
>>>>' Can't find the name 'l'

>>you
>>>>entered in the expression.
>>>
>>>--------------

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

>>----------------

>John Parker wrote:
>
>>Unfortunately my version of MS Access 97 does not appear
>>to support the function 'Replace()'

>-----------------
>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