Referring Fields from the same Query

[AdmiralJonB]

I'm having a problem referring to fields that are in the same query.

What I have is one field with a large formula (called discount). I have another field with a formula (called price). I'm wanting to make a third field which takes the discount from the price. If i use the build command and get the right field names etc., when i go to load the query, it asks what both the price and discount are. I don't want to duplicate these formulas as i'm wanting to use them in other fields in the same query.

Is there anything I'm doing wrong?

 

[Mile-O]

You're not doing anything wrong.

It can't use fields that are being calculated at "runtime" within the same query.

Save the query as is with the two fields calculated.

Build a second query, based on the saved query, and you can refer to the calculated fields all you want now.

 

[AdmiralJonB]

Thanks a lot, this was the final thing i had to do with this database. I'm glad to be seeing it gone!

 

[Sergeant]

It can't use fields that are being calculated at "runtime" within the same query.

I'm wondering if I understand that correctly.
This works:

SELECT tblPrices.UnitPrice, 0.1 AS Discount, [Discount]*[UnitPrice] AS DiscountAmt, [UnitPrice]-[DiscountAmt] AS FinalPrice
FROM tblPrices;

Shouldn't it?