Eval() Problem

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

  1. John Parker

    John Parker Guest

    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
    #1
    1. Advertisements

  2. 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
    #2
    1. Advertisements

  3. John Parker

    John Parker Guest


    >-----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
     
    John Parker, Mar 14, 2004
    #3
  4. 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]
     
    Marshall Barton, Mar 15, 2004
    #4
  5. John Parker

    John Parker Guest

    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
    per your coding.
    >
    >Marsh your a Star! Many Thanks
    >
    >John Parker
     
    John Parker, Mar 15, 2004
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bill Mitchell

    Problems with EVAL Function...

    Bill Mitchell, Jul 30, 2003, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    185
    Bill Mitchell
    Jul 30, 2003
  2. NH

    Problem with eval function

    NH, Mar 15, 2004, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    123
  3. Carl Rapson

    Using Eval to call a control event

    Carl Rapson, Jan 27, 2005, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    271
    Carl Rapson
    Jan 28, 2005
  4. António Castro Caldas

    EVAL

    António Castro Caldas, Jun 29, 2005, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    165
    John Nurick
    Jun 30, 2005
  5. Guest

    Populating variables M1 thru M50 via Eval?

    Guest, Aug 10, 2005, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    137
    Marshall Barton
    Aug 10, 2005
Loading...

Share This Page