Bitwise and Access

owain · July 18th, 2003, 07:31 AM

I have a column in an Access table that stores a bitwise value.

I now want to write a query just for testing purposes to see which bits have been 'turned on.'

When I try and use the AND bitwise operation against the column, all the values are returned as True. For example

Code:

SELECT (TYPE AND 2) FROM MyTable

Returns -1 (True) for all rows, even though I know that there are several that have not got the 2 bit turned on.

The possible values for the column are bits 1 OR 2 OR 4 OR 8 which represent a track type and optionally bits 536870912 (2 to the power 29) which represents active OR 1073741824 (2 to the power 30) which represents a save point.

Ultimately I want to have a query that will have a column that displays the constant name for the first 4 bits and 2 other calculated columns that check if the last 2 bits are switched on.

My table data looks a little like this:

Code:

And I would like the query to return something like this:

Code:

TYPE        TrackType Active SavePoint
----        --------- ------ ---------
536870913   Create    True   False
536870914   Change    True   False
1073741824            False  True
536870916   Delete    True   False
8           Event     False  False
536870913   Create    True   False
536870914   Change    True   False
536870916   Delete    True   False
1073741824            False  True

The query I have written to do this is as follows:

Code:

SELECT TYPE,
       IIf(TYPE AND 1, 'Create',
       IIf(TYPE AND 2, 'Change',
       IIf(TYPE AND 4, 'Delete',
       IIf(TYPE AND 8, 'Event', '')))) AS TrackType,
       (TYPE AND 536870912) AS Active,
       (TYPE AND 1073741824) AS SavePoint
  FROM MyTable

However it returns the following results:

Code:

TYPE        TrackType Active SavePoint
----        --------- ------ ---------
536870913   Create    True   True
536870914   Create    True   True
1073741824  Create    True   True
536870916   Create    True   True
8           Create    True   True
536870913   Create    True   True
536870914   Create    True   True
536870916   Create    True   True
1073741824  Create    True   True

What am I doing wrong, or can it simply not be done from within Access?

Regards
Owain Williams

owain · July 18th, 2003, 09:29 AM

Apologies for my lengthy first post, I fear that it has put people off replying because they do not have the time to read and understand the post. Therefore I shall summarise my problem more briefly in an attempt to encourage you, the forum members to understand and hopefully solve this particular problem.

I have a column in an Access database table that I want to perform bitwise operations on. I want to check whether a certain bit has been turned on or not. For example, if the value is 3, then bits 1 and 2 are on, if it is 4 then only bit 4 is on and 1 and 2 are off, it is 9 then bits 1 and 8 are on and bits 2 and 4 are off.

I am trying to test for which bits are turned on by using the bitwise AND operator as part of a calculated column, so my SQL statement would be something like SELECT (BitWiseColumn AND 2) AS Bit2 FROM MyTable, however Access is returning True for all the rows, not just the ones with bit 2 turned on.

For example the value 3 has bit 2 turned on so you would expect it to be True, however the value 1 does not have bit 2 turned on so you would expect it to be False, instead Access claims that both values have bit 2 turned on.

Can anyone help me with this annoying problem? Is it an Access quirk, am I using the wrong syntax, is it my flawed understanding of bitwise operations, or can it simply not be done?

I thank you in anticipation and apologise once again for the lengthy and complicated posts.

Regards
Owain Williams

Jeff Mason · July 18th, 2003, 09:42 AM

I think it is somewhat of an annoying Access thing.

AND and OR operators are generally logical operators, combining values which are interpreted as TRUE or FALSE and returning TRUE or FALSE according to the operation performed. A zero is interpreted as FALSE, and a non zero value is interpreted as TRUE. Thus, 3 AND 2 will always return TRUE since 3 is TRUE and 2 is TRUE and TRUE AND TRUE results in TRUE.

I think you can force a bitwise evaluation by assigning the result to a numeric value, as x=(3 AND 2) would return 2 as the value of x, as you would like.

I will avoid the obvious observation that the offending column in your table design violates first normal form :D.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · July 18th, 2003, 09:57 AM

I knew that someone was going to mention normalisation. It is not the prettiest of database designs I have ever come up with, however it is a column that started out in life with one purpose and has gradually adopted other purposes. Never mind, maybe I will scrap it entirely and start again with a more thoroughly thought out database design.

In any event, thank you for your reply Jeff, what you said does make sense. How would you go about implementing your suggestion of using x=(3 AND 2) in a SQL query, or is it a solution designed for the front end?

Regards
Owain Williams

Jeff Mason · July 18th, 2003, 10:19 AM

Perhaps you could write a VB function in a .bas module that did the bitwise AND and invoke it appropriately in your query? You could have one function that takes TYPE as input and returns the 'Create', 'Change', etc. string as appropriate. Other functions could handle the ACTIVE and SAVEPOINT columns similarly.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · July 18th, 2003, 10:33 AM

Ah well, VB it is then. Because I only want a solution for testing I was hoping to be able to create a quick and dirty SQL query and not have to resort to a VB project.

Maybe it is the way I am reading your last post, however I am confused as to what you mean when you said 'invoke it appropriately in your query.' Do you mean test the value once the recordset has been created, or are you implying that I can use a VB function in an Access SQL query?

Regards
Owain Williams

Jeff Mason · July 18th, 2003, 10:51 AM

I don't think you have to resort to a full blown Access app.

You could write a function in a module, say, MyFuncs.bas, and invoke it directly in your query:

Code:

SELECT TYPE, YourTrackTypeFunction(TYPE) as TrackType, ...

the function would take a long(?) as an input parameter and return a string:

Code:

Public Function YourTrackTypeFunction (byval TheType as long) as string
    If TheType AND 1 = 1 then YourTrackTypeFunction="Create"
    ElseIf TheType AND 2 = 2 then YourTrackTypeFunction="Change"
    ...
    End Function

You get the idea...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

owain · July 18th, 2003, 11:01 AM

Bloody hell, that is rather nice. I didn't know you could use VB functions in Access. I think that I will be using that facility a lot in the future! An I thought UDF's were unique to SQL Server.

Thanks once again uncle Jeff ;)

Regards
Owain Williams

Jeff Mason · July 18th, 2003, 11:21 AM

Actually, it's not all that nice, SQL wise, IMO.

It make you think wrong: viewing rows as records one at a time, rather than as a set of rows which you operate on all at once as a set.

That's the reason I tend to stay away from UDF's in SQL Server.

Still, the functions do have their uses...

Note that your problem disappears if you correctly normalize the data. You'd have one column for each use with a simple integer value, say, to represent 'Create', 'Delete', etc. for 'TrackType', another column for 'Active' containing, say, 1 or 0, and yet another similar one for SavePoint. You could even store the strings 'Create', Delete', etc. as the 'TrackType' column value.

Columns are cheap, within reason. Disk space is cheap. Non normalized data is going to cost somebody a lot of money somewhere down the road. It's not a question of whether, just when...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Nick Meyer · January 19th, 2006, 07:39 PM

While this post is quite old, I came across it while googling "bitwise operations ACCESS queries." I use the trick of storing multiple check box values as integers like 00010001, which then gets truncated to 10001. I don't convert the results to integer (17 in the example) so I don't get brain-ache when troubleshooting. The bugger comes in when you want to do a bitwise operation to mask a value in an Acess query (ie 10001 & 10000 which should = 10000). So a calculated field like MyField:iif([somebitwisefield] & 10000 = 10000 ,true,false) doesn't work in the expected bitwise fashion because the query grid doesn't do bitwise operations. What to do what to do?

This works instead for the last bit (00001):
IIf(([somebitwisefield]-1) Mod 10=0,-1,0)

The next bit
IIf(([somebitwisefield]\10 -1) Mod 10=0,-1,0)

the third bit
IIf(([somebitwisefield]\100-1) Mod 10=0,-1,0)

and so on.

How does it work?

The backslash '\' operator is integer division --it discards the remainer. For each power of 10 a bit is therefore discarded. (ie 11111 \ 100 equals 111, so does 11110 \ 100 or 11100 \100). Next subtract 1. If the desired bit WAS a 1, you now have a number that will divide evenly by 10. So the expression "MOD" 10 will return 0 if the desired bit was a 1, or something else if it wasn't. (The "MOD" operator returns the remainder of a divison operation)

This technique allows me to do a bitmasking operation for a calculated field in an access query with relative brevity. Hopefully the next poor bugger who wants to do the same finds this helpful.

Note: if you store your values as actual integers (ie 17 and not 10001 life gets a little hard because you are going to need to convert it back to bits first--another reason I just leave the raw binary