Conditional Calculations

[Syed Zeeshan Haider]

Hello Everybody,
I am quite a newbie in MS Access. Queries are the toughest for me. However,
I have managed to write some queries which are returning quite accurate
results.
Now I am trying to do some calculations in a query based on conditions. The
calculations can be simply described as following:

If x > y Then
x - y
ElseIf x < y Then
x + z - y
End If

Yes, I wrote it in VB style. I haven't been successful doing this in MS SQL
in an Access query.

How can I do above-mentioned *conditional calculation* in an Access query?

Thank you,

 

[Baz]

Hello Everybody,
I am quite a newbie in MS Access. Queries are the toughest for me. However,
I have managed to write some queries which are returning quite accurate
results.
Now I am trying to do some calculations in a query based on conditions. The
calculations can be simply described as following:

If x > y Then
x - y
ElseIf x < y Then
x + z - y
End If

Yes, I wrote it in VB style. I haven't been successful doing this in MS SQL
in an Access query.

How can I do above-mentioned *conditional calculation* in an Access query?

Thank you,
--
Syed Zeeshan Haider


-------------------------------------------------------------------------- -----------------------------
Download a free game to play with Internet Explorer from
http://szh.20m.com/entertainment/olwg.html

Hi Syed,

The trick is to figure out how to do it without using the clumsy Iif
(Immediate if) function. In your example, this will do the trick:

x-y-((x>y)*z)

Explanation:

1. x+z-y is the same as x-y+z, so, either way, we start off with x-y
2. x>y will be either True (which, in Access, means a numeric value
of -1) or False (i.e. 0). So, if it's true, then (x>y)*z = -z. If it's
false, (x>y)*z = 0

Incidentally, you didn't say what should happen when x=y.