CODEWITHSUNDEEP
ms-access
Brant Barton
Brant Barton

Reputation: 458

Reference existing expression

In MS Access, how do I reference an existing expression in the builder?

I tried just typing it in as: Expr1, and I also tried putting it in brackets like this: (Expr1), in both cases, the number that came out didn't make sense.

Update:

Currently I have these existing Expressions

SELECT tblInventory.ProductNo, tblInventory.NewFSPrice, tblInventory.FSPrice,     tblInventory.FSMarkUp, tblInventory.Cost, [tblInventory]![Cost]/[tblInventory]![FSMarkUp]      AS Expr1, [tblInventory]![Cost]/[tblInventory]![FSPrice] AS Expr2
FROM tblInventory;

If it really comes down to me having to bracket these and then run them I will, but I would like to know if there's a way that I don't have to actually write them out again so I can use them in an expression that would be something like

FSPrice - Expr1

Upvotes: 2

Views: 2202

Answers (1)

Fionnuala
Fionnuala

Reputation: 91326

Here is my query

SELECT tblInventory.ProductNo, 
       tblInventory.NewFSPrice, 
       tblInventory.FSPrice,  
       tblInventory.FSMarkUp,  
       tblInventory.Cost,  
       [tblInventory]![Cost]/[tblInventory]![FSMarkUp] AS Expr1,  
       [tblInventory]![Cost]/[tblInventory]![FSPrice] AS Expr2,  
       [FSPrice]-[Expr1] AS Expr3
FROM tblInventory;

Returning

ProductNo   NewFSPrice  FSPrice FSMarkUp    Cost    Expr1   Expr2   Expr3
1           10          10      5           5       1       0.5     9

Note that Expr3 must occur after Expr1 in the design grid. In addition, I am not dividing by zero at any point, nor are any nulls returned, if there were, I would need to allow for that by using IIfs and / or Nz.

Upvotes: 2

Related Questions