By elphj


2009-04-21 13:00:04 8 Comments

Can you use case expressions in Access? I'm trying to determine the max date form 2 columns but keep getting syntax errors in the following code:

CASE 
  WHEN dbo_tbl_property.LASTSERVICEDATE > Contour_dates.[Last CP12 Date]
    THEN dbo_tbl_property.LASTSERVICEDATE 
  ELSE Contour_dates.[Last CP12 Date] 
END AS MaxDate

2 comments

@Mitch Wheat 2009-04-21 13:03:03

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.

@elphj 2009-04-21 13:03:41

i thought you had to have a true flase answer to do that?

@Binary Worrier 2009-04-21 13:08:29

"dbo_tbl_property.LASTSERVICEDATE > Contour_dates.[Last CP12 Date]" is your true/false

@juckobee 2009-04-21 13:36:05

There is no case statement in Access. Instead you can use switch statement. It will look something like the one below:

switch(dbo_tbl_property.LASTSERVICEDATE > Contour_dates.[Last CP12 Date],dbo_tbl_property.LASTSERVICEDATE,dbo_tbl_property.LASTSERVICEDATE <= Contour_dates.[Last CP12 Date],Contour_dates.[Last CP12 Date])

For further reading look at: http://www.techonthenet.com/access/functions/advanced/switch.php

Or for case function implementation example in VBA:

http://ewbi.blogs.com/develops/2006/02/adding_case_to_.html

Regards, J.

@Adam Porad 2015-10-04 06:19:32

The switch function is a much better function to use if there are more multiple conditions being evaluated. It will be easier to read/understand/maintain compared to using nested IIF functions. Also, an alternate way to implement a default value instead of creating a logical expressions that basically evaluates to "all the previous expressions are false", is to just use true as the final expression to be evaluated. For example switch(dbo_tbl_property.LASTSERVICEDATE > Contour_dates.[Last CP12 Date], dbo_tbl_property.LASTSERVICEDATE, true ,Contour_dates.[Last CP12 Date])

Related Questions

Sponsored Content

16 Answered Questions

[SOLVED] Error related to only_full_group_by when executing a query in MySql

10 Answered Questions

[SOLVED] SQL Switch/Case in 'where' clause

8 Answered Questions

[SOLVED] How do I do multiple CASE WHEN conditions using SQL Server 2008?

  • 2013-01-31 16:51:44
  • Nils Anders
  • 761965 View
  • 156 Score
  • 8 Answer
  • Tags:   sql sql-server-2008

24 Answered Questions

[SOLVED] Is it possible to insert multiple rows at a time in an SQLite database?

  • 2009-10-22 20:04:42
  • Andrew
  • 362263 View
  • 528 Score
  • 24 Answer
  • Tags:   sql sqlite syntax

2 Answered Questions

Converting sql case expression

  • 2016-06-10 19:00:20
  • Robert
  • 53 View
  • 0 Score
  • 2 Answer
  • Tags:   sql ms-access case

9 Answered Questions

[SOLVED] How to do INSERT into a table records extracted from another table

  • 2008-09-16 16:19:55
  • Martin08
  • 640058 View
  • 175 Score
  • 9 Answer
  • Tags:   sql ms-access

1 Answered Questions

Access Conditional formatting expression based on subform value (date)

  • 2016-03-07 11:32:03
  • Oldřich Švéda
  • 627 View
  • 0 Score
  • 1 Answer
  • Tags:   ms-access

2 Answered Questions

Convert CASE to IIF for an Access query

1 Answered Questions

[SOLVED] SQL / MS Access - why isn't this CASE working?

  • 2013-07-02 17:33:23
  • Jeff Brady
  • 111 View
  • 1 Score
  • 1 Answer
  • Tags:   sql ms-access case

Sponsored Content