Criteria For Calculated Field In Query

[james_IT]

Hi,

I have a calculated field in my query called "outstanding".

this is how the query looks:


Expr: SubFormTotal………………[qty]*[price]

Discount …………………………...[Discount]

Expr:Total………………………….[SubFormTotal]-[Discount]

Deposit…………………………….[Deposit]

BalanceToDate…………………….[BalanceToDate]

Expr: Outstanding…………………..[Total]-[Deposit]-[BalanceToDate]



PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields

what am i doing wrong and how can i solve it?

thanks

 

[RuralGuy]

Hi James,
I'm pretty sure you can *not* use the alias name in a Where clause. The entire formulas need to be repeated. Try:
Where ((([qty]*[price]) - [Discount]) - [Deposit] - [BalanceToDate]) > 0

 

[james_IT]

sorry, try that in which bit?

ive tried it and it keeps saying Undefined function where in expression or something?

 

[boblarson]

What RG was saying is that you created an alias called "SubFormTotal" and one named "Total" and you can't refer to those in the query. However, RG, that isn't the case as I have done it and just ran a test and it worked fine. So, something else is up with his stuff.

I'm editing this to show my test that worked:
SELECT [UnitPrice]*[Quantity] AS SubTotal, [SubTotal]*1.05 AS Total, [Total]-[SubTotal] AS AmtDifference
FROM [Order Details];
I used the orders table from Northwind as my table in the query.

 

[james_IT]

hmm...i did something and it worked...

i made a field outstanding:[EventTotal]-[EventDeposit]-[EventPaidToDate]

with the criteria as the where function suggested. i adjusted it slightly and something seemed to work.

I will double check this later and post back exactly what i did...

seems to be working ok though.

 

[RuralGuy]

Hi Bob,
I don't think you can use any of those aliases in a Where clause or an OrderBy clause. I could be wrong though. It wouldn't be the first time.

 

[pbaldy]

FWIW, I agree with RG. The problem as I understand it is that the WHERE clause is evaluated before the SELECT clause, thus the alias is unknown at that point.

 

[boblarson]

Yes, my brain went on space holiday there for a moment.