How to Check a Condition in Access Database

Question

Using Access Database

How to use case condition?

My Query

Select 
    ID, 
    Name, 
    Intime, 
    case when Outtime=Intime then ‘000000’ else Outtime end as Out 
from table

The above query was accepting in SQL 2000, but not accepting in Access Database

How I have to check the conditions?

Need Query Help.

Answer 1

MS Access uses JET SQL, not T-SQL like in MSSQL.

Answer 2

I believe you can use the IIF function.

Select ID, Name, Intime, IIF(Outtime=Intime,‘000000’,Outtime) as Out
from table

Taken directly from the documentation:

IIf(expr, truepart, falsepart)

The IIf function syntax has these arguments

 Argument  |  Description  
 expr      |  Required. Expression you want to evaluate.  
 truepart  |  Required. Value or expression returned if expr is True.
 falsepart |  Required. Value or expression returned if expr is False.

Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

Answer 3

If you clean up your code -- rename your column correlation name 'out' (it's a reserved word), change your keywords to uppercase, etc -- then according to the Mimer SQL-92 validator your SQL is valid Intermediate SQL-92 code. Therefore, you should have a reasonable expectation that this vanilla SQL code would work on any modern SQL implemenation.

Sadly, the Access database engine is not a modern SQL implementation and it is not SQL-92 compliant at any level.

The problem here is that the Access database engine does not support the CASE expression. Instead, it has a own IIF() expression. Normally I could make excuses by adding the qualifier, "...because the Access database engine shares an expression service with VBA." The thing is, in this case it doesn't: the IIF() expression in Access database engine SQL works differently from the VBA IIF() expression e.g.

SELECT IIF(0 = 1, 55 / 0, 99)

Works fine, returns the value 99.

? IIf(0 = 1, 55 / 0, 99)

Fails with 'Division by zero error'.

FWIW if you do need VBA and SQL to work the same, consider the SWITCH expression

SELECT SWITCH(1 = 1, 55, TRUE, 55 / 0)

? Switch(1 = 1, 55, TRUE, 55 / 0)

Both fail with 'Division by zero error'.

The Access database engine SQL has expressions not found in VBA (e.g. IS NULL, IN(), etc), so why no CASE expression? We shall probably never know. The Access database engine is poorly documented; specifically, there was never never much to begin with, what we do have is full of errors (material as well as errors of omission) and, because of the engine's age (i.e. old), what documentation exists is gradually disappearing from MSDN and Microsoft's website. The old Jet experts have moved on to other projects.

Answer 4

Access SQL has a couple of built-in functions that provide conditional evaluation of scalar values. IIf (already mentioned) evaluates a single condition and returns one of two values. It can be arbitrarily nested. There's also a Switch statement, which takes an arbitrarily long series of pairs of arguments. If the first argument is true, the second argument is returned, if the third argument is true, the fourth is returned, and so forth.

Switch(outtime=intime, '000000', 
       outtime='something else', 'some other value', 
       true, 'default value')

Sounds like IIf is the better option for your specific case, but for certain situations, Switch can be a good alternative.