Query calculation help needed

[Sketchin]

I have an expression as such:

LeaseCost: IIf([tblCompanies].[Portfolio]=0,Sum(([DailyRate]*[workingdays])),0)

How would I go about summing [LeaseCost]? I tried:

PreDiscount: Sum(IIf([tblCompanies].[Portfolio]=0,(Sum([DailyRate]*[workingdays])),0))

but I get an error saying "Cannot have aggregate function in expression"

I wish I could just do Prediscount: Sum([LeaseCost])

THanks

 

[accessAnswer]

have you tried :

(Sum(IIf([tblCompanies].[Portfolio]=0,(Sum([DailyRate]*[workingdays])),0))) as LeaseCost, Sum([LeaseCost])

 

[Sketchin]

It doesn't like the "As" part. Says invalid expression

 

[Sketchin]

Figured it out...just putting =Sum([Leasecost]) on my report in a textbox, rather than doing the calculation in the query itself.

Thanks for the input though!

 

[accessAnswer]

Sorry, I if you are in Design View of Query :

1st column:

LeaseCost: IIf([tblCompanies].[Portfolio]=0,Sum(([DailyRate]*[workingdays])),0)


2nd column:

PreDiscount: Sum(LeaseCost)


The query would first evaluate LeaseCost, and next go on to Taking value in LeaseCost column ( number) and evaluate PreDiscount

So it won't evaluate this huge equation again to calculate PreDiscount

 

[accessAnswer]

Great! Good luck!