If Else Statements

[Carlos]

What is the syntax to write an if Else statment in a query.

I want it to say if number = 9 do this elseif number = 8
do this etc

thanks

 

[Michel Walsh]

Hi,


In SQL, we do not say what to do but what to get. The database engine
determines what to do, once it knows what to get, what to retrieve.

SELECT iif( condition, thisIfTrue, thatIfFalseOrNull ) FROM ....


would instruct SQL to retrieve thisIfTrue is the condition evaluates to
TRUE, otherwise, retrieve the third argument (which can be an expression).
If you have many conditions, you can try a SWITCH:


SELECT SWITCH( condition1, value1, condition2, value2, ..., ..., TRUE,
DefaultValue) FROM ...


which instruct the database to return value1 if condition1 is true, else,
value2 if condition2 is true, else... until a condition that evaluates to
true, then, return its associated value.

If you use MS SQL Server, you would use CASE instead:

SELECT CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ...
ELSE DefaultValue END FROM ...



which is a little bit harder to understand, first time we see it, but
compare it to the SWITCH statement, and you should see that the punctuation
of the SWITCH is simply replaced with some fancy phrasing....



Hoping it may help,
Vanderghast, Access MVP

 

[jmonty]

Look up the IIf function in help.
Let's say I want the value of x to be A if y=9 and x=B if
it isn't.

x = IIf(Y = 9, "A", "B")

You can nest functions also. For your example:
If y = 9 then x = A
if y = 8 then x = B
else x = C


x= IIF(Y = 9, "A", IIF(Y = 8, "B", "C"))

 

[Guest]

Maybe the solution is to create a lookup table, with two
fileds tblLookupTheNumber,WhatToDisplay) and then make
a JOIN between your table and tblLookup?

True, you can use IIF, even nested IIF would work,
Switch, Choose etc., but all of those are hard to write,
read and understand when used in queries.