Geek asks and answers

Using the iif clause in Ms Access for a column with a Yes / No data type

I have a column with a name DayShiftin a table with a data type Yes/No(Boolean). The result that I want to get: if the value is true, the Night display still shows night.

I tried the following:

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;

But none of this works. It just gives me a list of empty checkboxes in the output window. I am using Ms Access 2003. Any help is appreciated.

Update:

After a little research, the yes / no data type in Ms Access 2003 cannot handle null values ​​appropriately. Therefore, a mistake. See this link for details.

Update 2

Real request with associations. I have not mentioned this since I received the information presented above.

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;
+3
5

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 (false/no).

+3

, ....

Null DayShift, Access . :

 iif(Nz(DayShift,0)=-1,"Day","Night")
+1

:

SELECT IIf([Table1]![isDayShift]=True,"Day","Night") AS Shift
FROM Table1;

Access 2007 (, 2003 ). , , , . . , , , .

- .

0

"DayShift", "Shift" .

(=True) , /.

0

The Access database engine (ACE, Jet, any) has a smart / dumb function that allows an expression in a sentence SELECTto refer to a sentence AS("column alias") in the same way SELECTif the sentence ASis to the left of the expression. This makes it easy to test expressions with test data without using a table at all (assuming ANSI-92 Query Mode , I think), for example. try to perform any of the following statements separately: everyone should work and produce the expected result:

SELECT CBOOL(TRUE) AS DayShift, 
       IIF(DayShift = TRUE, 'Day', 'Night') AS Shift;

SELECT CBOOL(FALSE) AS DayShift, 
       IIF(DayShift = TRUE, 'Day', 'Night') AS Shift;

SELECT NULL AS DayShift, 
       IIF(DayShift = TRUE, 'Day', 'Night') AS Shift;

SELECT CBOOL(TRUE) AS DayShift, 
       IIF(DayShift, 'Day', 'Night') AS Shift;

SELECT CBOOL(FALSE) AS DayShift, 
       IIF(DayShift, 'Day', 'Night') AS Shift;

SELECT NULL AS DayShift, 
       IIF(DayShift, 'Day', 'Night') AS Shift;
0
source

More articles:


All Articles