Case expressions in Access

Question

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

Answer 1

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.

Answer 2

FWIW - IIF is a drag, and the switch solution doesn't seem valid for SQL (I may have done something wrong). I entered the values that Fionnuala offered into a new table named AccessObjectXref:

ID ObjectType ObjectDesc 1 -32768 Form 2 -32766 Macro 3 -32764 Report 4 -32761 Module 5 -32758 Users 6 -32757 DB Document 7 1 Table 8 2 DB 9 3 Container 10 5 Query 11 8 Subdatasheet

Then used the following SQL to create a list of object names and their counts. Obviously you could include every record if you wanted:

SELECT objectdesc, Count(*) AS Expr1 
FROM msysobjects, AccessObjectTypeXref 
where type = objecttype group by objectdesc order by objectdesc

I got the list of object types from: Meaning of MsysObjects values

Answer 3

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.