Help with an IFF Statement

[theRepublic]

Hey Database Experts

In my query I have made a Calculated Field named "Subtotal".

Here is my IFF statement for this field.

=IIF([DiscountRate] =”0”, Subtotal=[Quantity]*[Rate], Subtotal=[Quantity]*[DiscountRate])

Can anyone tell me how to fix this statement?

Pretty much Im trying to state that IF the Discount Rate is equally to Zero, than Subtotal is equal to Quantity * Rate, Otherwise, Subtotal is equal to Quantity * Discount Rate

Make Sence? I hope so. Thanks for the help

 

[pbaldy]

I doubt you want the zero in quotes. Try without them.

 

[theRepublic]

I have taken out the "" however I get an error msg stating the following:

Circular reference caused by alias 'Subtotal' in query definition's SELECT list.

Here's what I know. The IIf() has three parts - condition, true path, false path.

= IFF(DiscountRate is NULL), than the true path is Subtotal will equal to Rate*Quantity, Otherwise Subtotal will equal to DiscountRate*Quantity.

That is what I am trying to convey. Any ideas?

 

[doco]

Try fully qualifying your subtotal field. That way the ambiguity will be eliminated. In other words you must have more than one field with the name subtotal so: table_one.subtotal; table_two.subtotal removes all doubt - well mostly

BTW republic: you and Banana should get together

 

[theRepublic]

Hey,

I solved it myself!!

This is the proper code:

Subtotal: IIf([DiscountRate] Is Null,[Term(months)]*[Rate],[Term(months)]*[DiscountRate])

First empowering moment in Access for a newb like me..

Later

 

[theRepublic]

Hey Patman,

I didnt see your reply, but I solved it anyways. Your way works too. Thanks for the advice.