Bitwise Access vs Excel

  • Hi All,


    I've come across a really confusing problem, when I was attempting to build a workaround to the lacking Bitwise AND in access.


    In excel - formulas in the header

    AB : =MOD($A2/(2^0),2)>=1
    C : =MOD($A2/(2^1),2)>=1D : =MOD($A2/(2^2),2)>=1E : =MOD($A2/(2^3),2)>=1
    1TRUEFALSEFALSEFALSE
    2FALSETRUEFALSEFALSE
    ...
    14FALSETRUETRUETRUE
    15TRUE
    TRUETRUETRUE


    In Access using the same data in column A but in an access table:

    SELECT ModuleBitWise,

    ((ModuleBitWise/(2^0)) Mod 2) >=1 as Result0,

    ((ModuleBitWise/(2^1)) Mod 2) >=1 as Result1

    ((ModuleBitWise/(2^2)) Mod 2) >=1 as Result2

    ((ModuleBitWise/(2^3)) Mod 2) >=1 as Result3

    FROM STATIC_DATA

    I get the below:

    1-1
    000
    20-100
    ...
    140-100
    15-1000


    Does anyone know what's going on here? I'm stumped and been looking at this so long that my brain is now mush. Excel is behaving as expected however Access isn't.



    Thanks,


    Nick

  • Sorry to waste anyones time who's already spent time looking at this, but I believe the issue is related to Access rounding the results before running them through mod, as Access Mod only allows Ints. So (3/(2^1) aka 1.5 becomes 2 and 2 mod 2 = 0. solution to make access behave like excel is:

    (Int(ModuleBitWise/(2^0)) Mod 2) >= 1. This Int() forces the result of (ModuleBitWise/(2^0)) to be roundeddown to the closest whole number


    Thanks


    Nick