case when in SQL

L

loba

I have been using
select case when variable>something then something
when variable=something then something
else something
end
from table;
Can someone show me how in ACCESS? Thanks
 
J

Jeff Boyce

Access HELP - "case"

The specific syntax you'd use in a procedure is spelled out (with examples)
in HELP.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John Spencer

You have four options in Access SQL.

IIF function:
IIF(Test,ResultIfTrue,ResultIfFalse)
You can nest these
SELECT IIF(Field1 = 2,22, IIF(Field1 = 5,33,Null)) as Result
FROM Table

VBA Switch function - see VBA help
Switch(Test1, Result1, Test2,Result2,...)
SELECT Switch(Field1=2,22,Field1=5,33, True,Null) as Result
FROM Table

VBA Choose function - select the nth item in the list
Choose(IntegerNumber,Option1, Option2, Option3)
SELECT Choose(Field1,Null,22,Null,Null,33) as Result
FROM Table

Design a custom VBA function to return the value you want.
SELECT fGetValue(Field1) as Result
FROM Table

'Add the function to a VBA module so you can access it.
Public Function fGetValue(vIN)
SELECT Case vIN
Case 2
fGetValue = 22
Case 5
fGetValue = 33
Case Else
fGetValue = null
END SELECT
End Function


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
L

loba

Thanks John.
--
Barry


John Spencer said:
You have four options in Access SQL.

IIF function:
IIF(Test,ResultIfTrue,ResultIfFalse)
You can nest these
SELECT IIF(Field1 = 2,22, IIF(Field1 = 5,33,Null)) as Result
FROM Table

VBA Switch function - see VBA help
Switch(Test1, Result1, Test2,Result2,...)
SELECT Switch(Field1=2,22,Field1=5,33, True,Null) as Result
FROM Table

VBA Choose function - select the nth item in the list
Choose(IntegerNumber,Option1, Option2, Option3)
SELECT Choose(Field1,Null,22,Null,Null,33) as Result
FROM Table

Design a custom VBA function to return the value you want.
SELECT fGetValue(Field1) as Result
FROM Table

'Add the function to a VBA module so you can access it.
Public Function fGetValue(vIN)
SELECT Case vIN
Case 2
fGetValue = 22
Case 5
fGetValue = 33
Case Else
fGetValue = null
END SELECT
End Function


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...
 
Top