Affordable / reactive equivalent of Oracle decoding

Question

Is there an equivalent for Oracle decode () in Access (or Jet, for that matter).

The problem I ran into is this: I have to sort (order) a set of results based mainly on status and date (with all records having status = 2) at the end.

In Oracle, I would do something like

select
  ...
from
  ...
where
  ..
order by
  decode(status, 2, 0, 1),
  date_column

Answer 1

The closest analogy is a function SWITCH(), for example.

Oracle:

SELECT supplier_name,
       decode(supplier_id,  10000, 'IBM',
                            10001, 'Microsoft',
                            10002, 'Hewlett Packard',
                                   'Gateway') result
  FROM suppliers;

Database access mechanism

SELECT supplier_name,
       SWITCH(supplier_id = 10000, 'IBM',
              supplier_id = 10001, 'Microsoft',
              supplier_id = 10002, 'Hewlett Packard',
              TRUE, 'Gateway') AS result
  FROM suppliers; 

, SWITCH() , _. , , , . 1 = 1 TRUE:)

-, , , SWITCH() , , . , IIF().

Answer 2

IIF. . .

Answer 3

, .

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

- http://office.microsoft.com/en-us/access/HA012289181033.aspx

Choose(index, choice-1[, choice-2, ... [, choice-n]])

- http://msdn.microsoft.com/en-us/library/aa262690%28VS.60%29.aspx

Answer 4

You can use the function SWITCH:

LABEL: Switch(
   [TABLE_NAME]![COL_NAME]='VAL1';'NEW_VAL1';
   [TABLE_NAME]![COL_NAME]='VAL2';'NEW_VAL2';
)

Mark semicolons, not commas.

The example above works in queries in MS Access 2010.