Results 1 to 6 of 6
  1. #1
    eschmidt12 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    8

    Reference result of expression in another expression

    Is it possible to reference the result of an expression in the formula for another expression, all within the same query?

    For example:

    Expr1: [Table1]![Field1] * [Table2]![Field2]
    Expr2: [Expr1] * [Table3]![Field3]

    Is this possible, or do I have to create a subquery? I would like to avoid recalculating as much as possible...the expressions in my queries are much more complicated than this and they are being performed on large sets of data.

    Thanks!

    Evan

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I have gotten it to work, as long as all my intermediate calculations are "shown" (returned in the query).
    If you try not to show it, it doesn't like it so much.

    Maybe your example is an oversimplification of what you are doing, but if not, note that you can simply write your second expression as:
    Code:
    Expr2: [Table1]![Field1] * [Table2]![Field2] * [Table3]![Field3]
    and then you bypass that problem altogether.

  3. #3
    eschmidt12 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    8
    Quote Originally Posted by JoeM View Post
    I have gotten it to work, as long as all my intermediate calculations are "shown" (returned in the query).
    If you try not to show it, it doesn't like it so much.

    Maybe your example is an oversimplification of what you are doing, but if not, note that you can simply write your second expression as:
    Code:
    Expr2: [Table1]![Field1] * [Table2]![Field2] * [Table3]![Field3]
    and then you bypass that problem altogether.
    Thanks for the response. Do you know exactly what the syntax is for how you were able to get your expressions to reference other calculated fields?

    And yes, my example was a vast oversimplification of what I'm actually doing

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Just like you had it, except I gave my calculated fields meaningful names instead of the default "Expr...".

    Just be sure that the "Shown" box is checked on those intermediate calculated fields.
    I am not sure, but you might need to save it before after you create the intermediate calculation, and before you use it in the next calc.

  5. #5
    eschmidt12 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    8
    Hmm...when I do that I get the dialogue box asking for user input. Same thing happens if I explicitly reference the parent query as well.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It sounds like it is not recognizing the calculated field name.

    I have to admit, I have had a few occasions where I experienced that behavior, and had to resort to using subqueries or nested queries.

    If your calculation is complex, an alternative may be to create a User Defined Function, where you can just feed it the variables, and do the calculation in VBA. Then you might not have need for an intermediate calculation at all.

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

Similar Threads

  1. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  2. Replies: 3
    Last Post: 11-07-2011, 10:41 AM
  3. Expression builder error, circular reference
    By cowboy in forum Programming
    Replies: 3
    Last Post: 07-15-2010, 12:55 PM
  4. Replies: 3
    Last Post: 07-12-2010, 01:38 PM
  5. Replies: 1
    Last Post: 07-12-2009, 05:09 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