Calculated field :: Need an IIF statement?

  • Thread starter Thread starter Cameron Sutherland
  • Start date Start date
C

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
 
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
Cameron Sutherland said:
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


.
Click to expand...
Click to expand...
 
Back
Top