Support for CASE statements in SQL

  • Thread starter Thread starter Domenick
  • Start date Start date
D

Domenick

I know that Access 2003 does not support the ANSI SQL "CASE WHEN...ELSE..END"
statement. I know that you have to use an inline IF statement [IIF()]. Also,
I've read that an IIF will only allow for seven (7) conditions. I need to
resolve this so here are my questions:

1. Does Access 2007 support CASE statements in queries?
2. IF not, do you know if this is planned for the 2010 release? (I assume
all current versions of SQL Server support this, correct?)
3. How can I overcome the seven (7) condition limitation using Access 2003
(workarounds)?

Thanks for the help.
 
You can use Select Case within a function inside a module. Then you can call
upon it in a query/SQL statement. Below is a short example.

Function fTiers(strTiers As Variant) As String
Dim TheTier As String
Select Case strTiers
Case Is > -10000 ' greater than -10000
TheTier = "Tier1"
Case -1000000 To -10000
TheTier = "Tier2"
Case Is < -1000000 ' Number less than -1000000
TheTier = "Tier4"
Case Else ' Other values.
TheTier = "Not Tier"
End Select
fTiers = TheTier
End Function

Then in the query field put something like:

TheTier: fTiers([TheFieldName])
 
I just tried

SELECT iif( false, 0 , iif( false, 0, iif( false, 0, iif( false, 0,
iif(false, 0 , iif(false, 0, iif(false, 0, iif(false, 0, 1))))))))


with 8 iif, and it works, although it may be hard to maintain. You can use
SWITCH:

SELECT SWITCH( false, 10, false, 20, false, 30, null, 40, false, 50, false,
60, false, 70, null, 80, true, 90)


which is somehow easier to maintain. Note though that SWITCH evaluates each
argument, while iif evaluates only those which are required: iif( true, 1,
1 / 0 ) as example, won't evaluate 1 / 0.



Vanderghast, Access MVP
 
Back
Top