Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 17th, 2003, 10:56 AM
Registered User
Join Date: Sep 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Case Statements in Access

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

Old September 17th, 2003, 02:41 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

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
Old September 17th, 2003, 04:30 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

Closest thing I know in Access to the TSQL 'case' expression is the 'switch' function, e.g.
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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Switch Case statements ermy78 Access VBA 3 October 20th, 2006 08:58 AM
CASE Statements in T-SQL atcs2152 SQL Server 2000 3 April 28th, 2006 10:53 AM
Case Select Statements & Updating the Database jackiew General .NET 1 April 11th, 2006 11:47 AM
Case Statements cfriedberg SQL Server 2000 1 September 7th, 2005 08:46 PM
Selection Statements for MS Access gmoney060 Classic ASP Databases 4 July 18th, 2004 10:46 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.