sql - 在 Ms Access 中对数据类型为 "Yes/No"的列使用 iif 子句

Question

我在表中有一个名为 DayShift 的列，它是 Yes/No 数据类型( bool 值)。我想要的结果输出是:如果值为 true，则显示“Day”，否则显示 night。

我尝试了以下方法:

SELECT iif(DayShift=Yes,"Day","Night") as Shift FROM table1;

SELECT iif(DayShift,"Day","Night") as Shift FROM table1;

SELECT iif(DayShift=True,"Day","Night") as Shift FROM table1;

SELECT iif(DayShift=1,"Day","Night") as Shift FROM table1;

但是以上都不起作用。它只是在输出数据表窗口中给我一个空白复选框列表。我正在使用 Ms Access 2003。感谢任何帮助。

更新:

经过一些研究，Ms Access 2003 中的是/否数据类型无法正确处理空值。因此，错误。 Check this link for details.

更新 2

带有连接的真实查询。没有提到它，因为我认为上面提供的信息会起作用。

SELECT tblovertime.contfirstname                        AS [First Name], 
       tblovertime.contlastname                         AS [Last Name], 
       tblovertime.employeenumber                       AS [Employee Number], 
       tblsignup.thedate                                AS [Sign Up Date], 
       Iif([tblOvertime.DayShift] =- 1, "Day", "Night") AS shift, 
       (SELECT Mid(MIN(Iif(sector = 1, "," & sector, NULL)) & MIN( 
                               Iif(sector = 2, "," & sector, NULL)) & MIN( 
                               Iif(sector = 3, "," & sector, NULL)) & MIN( 
                           Iif(sector = 4, "," & sector, NULL)), 2) AS concat 
        FROM   tblempsectorlist 
        WHERE  tblempsectorlist.empnum = tblsignup.employeenumber 
        GROUP  BY empnum)                               AS sectors, 
       tblovertime.timedatecontact                      AS [Date Contacted], 
       tblovertimestatus.name                           AS status 
FROM   (tblsignup 
        INNER JOIN tblovertime 
          ON ( tblsignup.thedate = tblovertime.otdate ) 
             AND ( tblsignup.employeenumber = tblovertime.employeenumber )) 
       INNER JOIN tblovertimestatus 
         ON Clng(tblovertime.statusid) = tblovertimestatus.statusid 
WHERE  (( ( tblsignup.thedate ) ># 1 / 1 / 2011 # )) 
ORDER  BY tblsignup.thedate; 

Answer 1

你的第二个是对的

SELECT iif(DayShift,"Day","Night") as Shift FROM table1;

我建议尝试以下方法以查看实际评估的内容

SELECT iif(DayShift,"Day","Night") as Shift, DayShift FROM table1;

你同样可以做

SELECT iif(DayShift = -1,"Day","Night") as Shift FROM table1;

由于 MS Access 将 true 存储为 -1，将 false 存储为 0(它不像 true = 1 那样直观，但在双补语中计算可能更快)

-- 编辑--
由于您似乎正在使用一个联接，它可以导致是/否的 Nul，请使用 nz() 函数。

select iff(nz(DayShift, 0), "Day","Night") as Shift FROM table1;

当 DayShift 返回 null 时，结果将返回 0(假/否)。