Solved

Case Statement in Access Query

Posted on 2014-09-15
2
324 Views
Last Modified: 2014-09-15
Experts,

I would like to create a column in an SQL query in Acces.  I would usually do it with a Case Statement.

How would I do this.

If column_a = '777777' then column_b = 'CAT' else column_b = 'DOG'
0
Comment
Question by:morinia
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40323372
column_b: iif(column_a="77777", "CAT","DOG")

(Note that it is IIF, not IF).
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 40323416
SELECT CASE statements can't be used in queries. IIF works for simple comparisons but as there are more options, it gets more and more difficult to understand and maintain over time.

Rather than a SELECT CASE statement, you may be better off using a table with the value pairs then link to it. That'll be easier to manage as it'll be in data rather than code.

Alternatively, if you want to use the SELECT CASE statement, Access lets you call your own Functions in queries. This powerful feature lets you combine the power of queries and VBA. See the section of my white paper on Using Custom Functions in Microsoft Access Queries for more details.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question