Prev: Display Query Results on top of a Form
Next: New Blank Query Shortcut
From: Domenick on 13 Jan 2010 15:57
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.
From: Jerry Whittle on 13 Jan 2010 16:09
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])
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Domenick" wrote:

> 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.
From: vanderghast on 13 Jan 2010 16:26
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


"Domenick" <Domenick(a)discussions.microsoft.com> wrote in message
news:755C5206-0E7E-41C7-BE4D-4C0B9A1CDEBC(a)microsoft.com...
>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.

 | 
Pages: 1
Prev: Display Query Results on top of a Form
Next: New Blank Query Shortcut