IIF function alternative

[Jasper Recto]

Is there an alternative to the IIF function?

I use a different software that has a function called CASE. It works like
this:

CASE(expression, when-value-a, return-value-a, when-value-b, return-value-b,
.... ,
default-return-value)


This works like a nested IIF function

Thanks!
Jasper

 

[BruceM]

See Help for information about Select Case. In general it's something like
this, depending on the values you need to check (the syntax is a bit
different for text values, for instance):

Select Case SomeValue
Case = 1
MsgBox "One"
Case = 2
MsgBox "Two"
Case Else
MsgBox "More than two"
End Select

 

[Ryan]

Check out this page from microsoft on Case statements
http://msdn.microsoft.com/en-us/library/ms181765.aspx
I use case in all my apps, it make for a cleaner code and it is also eiser
to read and gives you less code to write if there are multiple selections to
choose from.

USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

 

[Klatuu]

Jet SQL does not support the When clause. That is SQL Server syntax.
If you are using Jet, you have some alternatives, depending on your needs
and the expected values in the field.
One is the Switch function, another is the Choose function, or you can write
a function in a Standard module that uses the Select Case statement to return
a value and you can use the function in a Jet query. Be aware, however, that
should you ever upsize to SQL Server, it will not work. SQL Server will not
understand VBA code.