Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Case Statement in Access Query

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
morinia
Asked:
morinia
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
column_b: iif(column_a="77777", "CAT","DOG")

(Note that it is IIF, not IF).
0
 
Luke ChungPresidentCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now