Case expressions in Access

by

You can use the IIF() function instead.

IIF(condition, valueiftrue, valueiffalse)

  • condition is the value that you want to test.

  • valueiftrue is the value that is returned if condition evaluates to TRUE.

  • valueiffalse is the value that is returned if condition evaluates to FALSE.

There is also the Switch function which is easier to use and understand when you have multiple conditions to test:

Switch( expr-1, value-1 [, expr-2, value-2 ] … [, expr-n, value-n ] )

The Switch function argument list consists of pairs of expressions and
values. The expressions are evaluated from left to right, and the
value associated with the first expression to evaluate to True is
returned. If the parts aren’t properly paired, a run-time error
occurs. For example, if expr-1 is True, Switch returns value-1. If
expr-1 is False, but expr-2 is True, Switch returns value-2, and so
on.

Switch returns a Null value if:

  • None of the expressions is True.

  • The first True expression has a corresponding value that is Null.

NOTE: Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for
undesirable side effects. For example, if the evaluation of any
expression results in a division by zero error, an error occurs.

More Related Contents:

  1. SQL Access ” Count “? [closed]
  2. How do I use parameters in VBA in the different contexts in Microsoft Access?
  3. SQL multiple join statement
  4. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  5. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  6. How to do INSERT into a table records extracted from another table
  7. Multiple INNER JOIN SQL ACCESS
  8. Ms Access Query: Concatenating Rows through a query
  9. is there a group_concat function in ms-access?
  10. How do I implement pagination in SQL for MS Access?
  11. [] brackets in sql statements
  12. Row numbers in query result using Microsoft Access
  13. can we list all tables in msaccess database using sql?
  14. SQL to add column with default value – Access 2003
  15. Use Access SQL to do a grouped ranking
  16. Access – compare two tables and update or insert data in first table
  17. “You tried to execute a query that does not include the specified aggregate function”
  18. How can I implement SQL INTERSECT and MINUS operations in MS Access
  19. SQL: Using NULL values vs. default values
  20. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  21. Editing Record issues in Access / SQL (Write Conflict)
  22. How do I create a table alias in MySQL
  23. How do I perform update query with subquery in Access?
  24. Select query with date condition
  25. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  26. Microsoft Access – Case Query
  27. SQL SET DEFAULT not working in MS Access
  28. CONCAT equivalent in MS Access
  29. Check if access table exists
  30. Access substitute for EXCEPT clause

Leave a Comment