Bit Wise Search

[Arnaud Migeon]

I have a table with a column that is used bit wise:
bit 0: => Export
bit 1: => Import
bit 2: => Europe
...
bit 7: => South America
User tick check box, but I can't use an SQL statement in ACCESS to query
using bit mask...
(3 and 1) = 1 is OK in VB but does not work in ACCESS SQL.
Can you help.

--
Cordialement,
Arnaud MIGEON
SDV Division Auxiliaire de Transport
Rsp. Domaine Commercial / Application Domain Leader
tél. : +33 1 34 21 44 69
fax. : +33 1 34 21 44 90
Parc d'Activité des Bellevues
2, Av. des Bellevues / 25, Av. du Gros Chêne
BP61 - 95610 ERAGNY / OISE
95612 CERGY PONTOISE - FRANCE

 

[Rob]

Not to sound preachy, but this is a glaring example of why
a DB field is supposed to store only a single piece of
data. If the option exists, you are probably best off
changing this into eight separate boolean fields.

That said, to resolve your immediate issue AFAIK you will
need to get creative with some functions to use in your
Access SQL to break out each of the bits into its own
column.

One way would be to use the Mod function (perhaps
"operator" would be a better description) and Integer
Divide like so:

[PackedVal] Mod 2 AS Export,
([PackedVal] \ 2) Mod 2 AS Import,
([PackedVal] \ 4) Mod 2 AS Europe,
....
([PackedVal] \ 128) Mod 2 AS SouthAfrica

Each of these functions would return a 0 if the bit was
false and a 1 if the bit was set.

FWIW to my knowledge SQL isn't able to handle bitwise
operations natively. Somebody please correct me if I am
wrong about that.

Rob

 

[John Vinson]

I have a table with a column that is used bit wise:
bit 0: => Export
bit 1: => Import
bit 2: => Europe
..
bit 7: => South America
User tick check box, but I can't use an SQL statement in ACCESS to query
using bit mask...
(3 and 1) = 1 is OK in VB but does not work in ACCESS SQL.
Can you help.

It's undocumented, but there are SQL operators BAND and BOR (binary
AND, binary OR). Try

[packedfield] BAND 1 = 1

as a criterion.

 

[Rob]

-----Original Message-----

I have a table with a column that is used bit wise:
bit 0: => Export
bit 1: => Import
bit 2: => Europe
..
bit 7: => South America
User tick check box, but I can't use an SQL statement in ACCESS to query
using bit mask...
(3 and 1) = 1 is OK in VB but does not work in ACCESS SQL.
Can you help.

as a criterion.>It's undocumented, but there are SQL operators BAND and BOR (binary
AND, binary OR). Try

[packedfield] BAND 1 = 1


.

Good to learn - are these common all SQL databases?

Rob

 

[Guest]

Soory, but this is not working,
It get the following Error:
Missing operator, BAND is selected
Using ACCESS 2000 for an ACCESS 97 database.

-----Original Message-----

-----Original Message-----

in
ACCESS to query

using bit mask...
(3 and 1) = 1 is OK in VB but does not work in ACCESS SQL.
Can you help.

as a criterion.>It's undocumented, but there are SQL operators BAND and BOR (binary
AND, binary OR). Try

[packedfield] BAND 1 = 1


.

Good to learn - are these common all SQL databases?

Rob
.

 

[Michel Walsh]

Hi,


I think it works only with ADO:

? CurrentProject.Connection.Execute( "SELECT 7 BAND 19").Fields(0).Value
3


while, with DAO

? CurrentDb.OpenRecordset("SELECT 7 BAND 19").Fields(0).Value

it produces an error. The query designer is based on DAO.



Hoping it may help,
Vanderghast, Access MVP

Soory, but this is not working,
It get the following Error:
Missing operator, BAND is selected
Using ACCESS 2000 for an ACCESS 97 database.

-----Original Message-----

-----Original Message-----

wrote:

I have a table with a column that is used bit wise:
bit 0: => Export
bit 1: => Import
bit 2: => Europe
..
bit 7: => South America
User tick check box, but I can't use an SQL statement

in
ACCESS to query

using bit mask...
(3 and 1) = 1 is OK in VB but does not work in ACCESS SQL.
Can you help.

as a criterion.>It's undocumented, but there are SQL operators BAND and BOR (binary
AND, binary OR). Try

[packedfield] BAND 1 = 1


.

Good to learn - are these common all SQL databases?

Rob
.

 

[Guest]

Rob,
1) Thank you for your answer. This work
2) For the preaching
..i) takes less space in the DB (true for SQL 6.5, false
with SQL 7, I don't know for ACCESS)
..ii) can evolves fast without breaking everything when you
need to manage some more check boxes...
..iii) you are right on the logical point of view.
3) SQL server did manage bit wise operator using C syntax.

-----Original Message-----
Not to sound preachy, but this is a glaring example of why
a DB field is supposed to store only a single piece of
data. If the option exists, you are probably best off
changing this into eight separate boolean fields.

That said, to resolve your immediate issue AFAIK you will
need to get creative with some functions to use in your
Access SQL to break out each of the bits into its own
column.

One way would be to use the Mod function (perhaps
"operator" would be a better description) and Integer
Divide like so:

[PackedVal] Mod 2 AS Export,
([PackedVal] \ 2) Mod 2 AS Import,
([PackedVal] \ 4) Mod 2 AS Europe,
....
([PackedVal] \ 128) Mod 2 AS SouthAfrica

Each of these functions would return a 0 if the bit was
false and a 1 if the bit was set.

FWIW to my knowledge SQL isn't able to handle bitwise
operations natively. Somebody please correct me if I am
wrong about that.

Rob

-----Original Message-----
I have a table with a column that is used bit wise:
bit 0: => Export
bit 1: => Import
bit 2: => Europe
...
bit 7: => South America
User tick check box, but I can't use an SQL statement in ACCESS to query
using bit mask...
(3 and 1) = 1 is OK in VB but does not work in ACCESS SQL.
Can you help.

--
Cordialement,
Arnaud MIGEON
SDV Division Auxiliaire de Transport
Rsp. Domaine Commercial / Application Domain Leader
tél. : +33 1 34 21 44 69
fax. : +33 1 34 21 44 90
Parc d'Activité des Bellevues
2, Av. des Bellevues / 25, Av. du Gros Chêne
BP61 - 95610 ERAGNY / OISE
95612 CERGY PONTOISE - FRANCE



.

.