Calculated field :: Need an IIF statement?

[Cameron Sutherland]

Everywhere you read people warn against IIF functions in
your queries because of the speed problems but sometimes
you just have to. Your query might look something like
this:
Price_Reduction:
IIF([Current_Price]=0, 0, [Original_Price]-[Current_Price])

In english this says if the current price is 0 then
display 0 otherwise perform calculation.

-Cameron Sutherland

 

[jason]

Yeah - plus, they are difficult to debug and decipher....I kinda wonder if
there is more elegant way to get the results I want in another way...any
ideas?

- Jason

Everywhere you read people warn against IIF functions in
your queries because of the speed problems but sometimes
you just have to. Your query might look something like
this:
Price_Reduction:
IIF([Current_Price]=0, 0, [Original_Price]-[Current_Price])

In english this says if the current price is 0 then
display 0 otherwise perform calculation.

-Cameron Sutherland

-----Original Message-----
I would appreciate help with an expression in the query builder of Access
2000:

I currently have a simple calculation field in my query which determines the
difference between two prices...

Price_Reduction: [Original_Price]-[Current_Price]

Works great....BUT....falls down when the Current Price is '0'.

EG:

20,000 - 0 = 20,000

Actually, if the Current_Price is Zero the Price_Reduction should = '0'

Like this:

Price_Reduction: 20,000 - 0 = 0 (No price reduction)

I guess it requires the use of perhaps an IIF statement but I am not
completely sure if this is correct. Can anyone give
me some guidance on how to construct this expression to reflect the reality
of the situation.?

Thanks in advance
Jason


.