MS Access WTF



  • I recently had to reverse engineer an old MS Access utility that stopped working as expected. Yes, I know the real WTF is that it uses Access, but it's a single user utility and the process it performs is actually quite in line with Access's capabilities. Basically it takes in a delimited text file, runs some queries, and exports another delimited text file.

    So as I'm stepping through the queries, I come across this little gem in one of the WHERE clauses.

    WHERE (IIf( Postdate >= Priordate, 0, IIf( Postdate = Asofdate, 0, 1) )
        OR
        IIf (IsNull(Checkno),0,1) ) =0
        
         
    Trust me, I cleaned it up a lot for readability but notice the placement of the OR as a bitwise operator instead of a comparison operator. I have a pretty good idea who actually wrote this utility so I found myself wondering whether it was ignorance or brilliance until I tested how Access treats the OR operator when used like this. I am now convinced it was ignorance.

    According to Access "0 OR 0 = 0". "0 OR 1", "1 OR 0", "1 OR 1" all return -1. Actually, anything except "0 OR 0" returns -1.

    And if you use AND in the same manner, anything with 0 returns 0.
     



  • *beep*

    Redundant subject line detected.



  • I'm not sure what the WTF here is (other than Access). OR behaves as expected, so does IIf. It's selecting all records satisfying Postdate >= Priordate, Postdate = Asofdate, and IsNull(Checkno). Maybe you're suggesting that the WHERE clause should be more like

    WHERE IsNull(Checkno) AND (Postdate >= Priordate OR Postdate = Asofdate)

    But that's not as 1337. Depending on how IIf is implemented in Access this may or may not be more efficient (though it probably is since wrapping IsNull(Checkno) in an IIf seems like a waste). Yeah. I guess I see the WTF now.



  • @mythbester said:

    According to Access "0 OR 0 = 0". "0 OR 1", "1 OR 0", "1 OR 1" all return -1. Actually, anything except "0 OR 0" returns -1.

    And if you use AND in the same manner, anything with 0 returns 0.

    I really don't see the WTF with that statement, and it's not according to Access, it's according to standard programming logic.  Any non-zero value is considered true.  Any zero value is considered false.  Therefore false OR false will always be false, where as false OR true will always be true...  Apply the same logic to the AND operation.  False AND true is always False...

    Granted most languages use 1 as the integer conversion of TRUE, VB (and it's derivatives) doesn't, it uses -1.   

     


  • Considered Harmful

    OR true false
    true true true
    false true false

     

    AND true false
    true true false
    false false false

    TRUE in Access is -1, but it seems to understand you mean 1 to be true as well; so non-zero can be considered true, and zero false. That seems like Access is doing things correctly.

    AND and OR can be bitwise or logical, where logical performs the operation on only one bit and bitwise does it on every bit of a number.

    I've never heard of OR used as a "comparison" operator, though.



  • Not just VB and its derivatives do this, but so do all BASICs. 

    I remember in the 80's being shown a "what not to do" example in BASIC:  A=B=C.  Unlike in C and its progeny (where A and B would be assigned whatever value was in C), if, in fact, B==C, then A gets assigned -1. otherwise it gets assigned 0.  (This stems from the WTF of BASIC's overloading of the = operator). 

    This worked in this manner on every BASIC I have ever tried it on.  The reason for this is that -1 is what you get when you set all bits of a 2's compliment integer to 1.  Whether this is preferable to just setting the least significant bit is left as an exercise for the reader, but it appears to have been a standard practice, apparently still carried forward, in BASIC. 

    Bottom line, though, BASIC has always used -1 as its "true" value, and that a boolean expression in some obscure flavour of BASIC should return -1 for a true expression does not surprise me.  Mind you, I haven't coded anything in any flavour of BASIC in over fifteen years.



  • You're right.  It's been at least a decade since I've heard anyone mention "basic" without "Visual" in front of it. 



  • @clively said:

    Granted most languages use 1 as the integer conversion of TRUE, VB (and it's derivatives) doesn't, it uses -1.   

    Bill Gates stole Time Machine from Apple and made sure that +1 maps to FILE_NOT_FOUND in advance of this site being created. And now that +1 is reserved, they chose the path of least possible effort, applied a bit of whiteout (or at least an eraser to the whiteboard they planned all this on) to the + sign, and presto bingo, -1 was born. 



  • @MarcB said:

    @clively said:

    Granted most languages use 1 as the integer conversion of TRUE, VB (and it's derivatives) doesn't, it uses -1.   

    Bill Gates stole Time Machine from Apple and made sure that +1 maps to FILE_NOT_FOUND in advance of this site being created. And now that +1 is reserved, they chose the path of least possible effort, applied a bit of whiteout (or at least an eraser to the whiteboard they planned all this on) to the + sign, and presto bingo, -1 was born. 

     

    Bill Gates huh?

    He must be quite the guy, having direct involvement in every minute part of the company like that.


Log in to reply