how to use CASE logic in a Access 2003 query? 1

[yehong]

I have this simple query but its not working. Keep getting error message that operator is missing.

"Select Employee.ID, (Case When (Employee.hire_Date IS NUll) Then '12/31/2020' Else Employee.hire_date End) AS Hire_Date
from employee;

 

[lameid]

Unless of course your backend is in SQL server, forget Transact SQL... You'll have to use Jet functions to accomplish the same thing. IIF function and Isnull is waht you need. Isnull tells you whether or not a value is null in JET. The equivalent funtion to the Transact Isnull in jet is NZ (null to zero). And Jet uses the hash (#) as the delimiter for dates...

Select Employee.ID, IIF(isnull(Employee.hire_Date) = yes, #12/31/2020#, Employee.hire_date End) AS Hire_Date
from employee;

 

[PHV]

Why not simply this ?
"SELECT ID, Nz(hire_Date,#2020-12-31#) AS Hire_Date FROM employee"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886