Case Statements in Access

dbegg · September 17th, 2003, 10:56 AM

Does anyone know whether there is an equivalent in Access to the TSQL Case Statement. Eg

Select Case
When Name = 'Fred' then 'Frederick'
When Name = 'Bill' Then 'William'
End, Street, Age
From Customer

Please dont tell me i have to do nested if statements

SerranoG · September 17th, 2003, 02:41 PM

Code:

Dim strChoice as String

Select Case Name
    Case "Fred"
        strChoice = "Frederick"
    Case "Bill"
        strChoice = "William"
    Case Else
        strChoice = Name
End Select

DoCmd.RunSQL "Select Name, Street, Age From Customer " & _
             "Where Name = '" & strChoice & "'"

If you mean in VBA try the above.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Jeff Mason · September 17th, 2003, 04:30 PM

Closest thing I know in Access to the TSQL 'case' expression is the 'switch' function, e.g.

Code:

SELECT switch(Name="Fred","Frederick",Name="Bill","William"), Street, ...

The arguments to the 'switch' function are pairs in the form: expression1, value1, ... value1 is returned if expression1 is true, etc.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com