MS Access - Query Help - IF statements 1

[belgravia]

I am setting up a query within a large contract database that tracks commissions. Within the query, I want to do some calculations based on 3 fields - Total Commission (which is the 5% of the purchase price), Co-op Broker? (a yes/no check box) and Co-op Commission. Co-op commission is 1/2 of the total commission minus $100. However, if Co-op Broker? is "no", then the co-op commission is $0. Right now my statement in the query is -

Coop Commission: IIf([Co-op Broker?]="No","$0.00",[Total
Commission]/2-100)

Right now, when I try to view the query, I get an "Empty Parameter box asking for a field that doesn't exist, "Total." If I click OK through this box, the resulting query will show the total commission, but the co-op commission will be empty, regardless of the state of the "Co-op Broker?" field. What is wrong with my If statement?

Thanks!
Mark

 

[lespaul]

You have to use the same formula referencing TotalCommission, not the "name" from the query. You can use names from queries in DIFFERENT queries, but not within itself. This should give you an idea:

SELECT (PurchasePrice * .05) As TotalCommission, iif(not [Co-op Broker?], 0, ((PurchasePrice * .05)/2)-100) As CoopCommission From tableName

 

[belgravia]

Seems to be working. Thanks!
Now the problem is if Co-op Broker is Yes, the right number appears but formatted like a normal integer (not currency). Properties say it should be currency, and I also put an input mask on the field but it does not work.
Thanks again,
Mark

 

[earthandfire]

Format(((PurchasePrice * .05)/2)-100),"currency")

Using "currency" as opposed to a mask in the expression ensures regional currency formatting

 

[PHV]

And this ?
SELECT (PurchasePrice * .05) As TotalCommission, CCur(IIf(Not [Co-op Broker?], 0, (PurchasePrice * .025)-100)) As CoopCommission From tableName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244