Case Statement in Access

Question

Can you use case statements in Access, im trying to det 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

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.

Answer 2

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.