# Eval() Problem

John Parker
Guest
Posts: n/a

 14th Mar 2004
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
Guest
Posts: n/a

 14th Mar 2004
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

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]

John Parker
Guest
Posts: n/a

 15th Mar 2004

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

Marshall Barton
Guest
Posts: n/a

 15th Mar 2004
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]

John Parker
Guest
Posts: n/a

 16th Mar 2004

>>>-----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
>
>Marsh your a Star! Many Thanks
>
>John Parker

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?TWF2aXJpY2s=?= Windows XP Help 2 23rd Apr 2006 02:55 PM =?Utf-8?B?VGhlIEF1c3NpZSBHaXJs?= Windows XP Performance 3 31st Mar 2005 12:23 PM =?Utf-8?B?TWlrZQ==?= Microsoft Dot NET 0 7th May 2004 10:46 PM Farzad Hayati Microsoft Windows 2000 Hardware 2 5th Feb 2004 11:22 PM Farzad Hayati Microsoft Windows 2000 Advanced Server 2 5th Feb 2004 11:22 PM

Features