PC Review


Reply
Thread Tools Rate Thread

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

 
Reply With Quote
 
 
 
 
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
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]
 
Reply With Quote
 
 
 
 
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"' !

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

 
Reply With Quote
 
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"' !

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

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

--
Marsh
MVP [MS Access]
 
Reply With Quote
 
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
per your coding.
>
>Marsh your a Star! Many Thanks
>
>John Parker


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


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


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:21 PM.