Results 1 to 5 of 5
  1. #1
    myoung2746 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6

    Eval function used on a query field to return a calculation

    Hello,



    I have an expression "Equation" in a query that concatenates 3 fields from a table to form to make a calculation

    Equation: [OperatorTextLeft] & [Variable1] & [OperatorTextRight]

    This returns "((4500/8.6)/11" (color coding corresponds to the source field listed in the Equation expression


    I would like to use the Eval function to calculate the numerical result of this expression, which is 47.6.

    I tried entering another expression in the same query EquationResult: Eval([Equation]), but that didn't work.

    What should I do to get it to evaluate the Equation Expression to 47.6?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Doesn't work doesn't help too much. You get an error message? No result? Wrong result?
    It should work, but not if any record contains null or empty string in even just one of those fields. In those cases, you'd need to update the field to 0 (as long as it won't be used in division) or use NZ to convert the missing value to some other value.
    Last edited by Micron; 01-08-2022 at 11:34 AM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    myoung2746 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6
    EquationResult: Eval([Equation]) outputs #Error

    I'm not sure the correct syntax to get it to output the correct answer for 47.6

  4. #4
    myoung2746 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    6
    Figured it out, to many parenthesis on the left side, should have been (4500/8.6)/11. I feel real smart

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I noticed that but assumed you just left one off at the end. Happens all the time; even when people cut and paste, something tends to get left off. I tested the ability of Eval to correctly process a concatenated equation and it worked for me, but my parentheses were as I thought yours would be.
    My bad.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-10-2015, 08:30 AM
  2. Conditional Formatting - Using Eval function with wildcards
    By mlichtenstein in forum Programming
    Replies: 2
    Last Post: 07-02-2015, 08:09 AM
  3. Replies: 3
    Last Post: 06-08-2014, 05:36 PM
  4. Access - Eval function
    By dodo47 in forum Access
    Replies: 19
    Last Post: 02-11-2012, 02:02 AM
  5. Eval function with variables
    By tuna in forum Programming
    Replies: 3
    Last Post: 05-14-2010, 06:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums