BITWISE anding in Query

  • Thread starter Thread starter Cliff
  • Start date Start date
C

Cliff

I have a query that is working fine with an SQL back end
but is not working with an ACCESS back end. The query
takes a db field and "AND"s a value with it. It returns
the value if there is a bit wise match of the value
contained in the db field.

For Example if the db value is 19 and the value being
anded is 16 the operation should return 16. With an SQL
back end all workd fine. But with an ACCESS backend if
get -1 in all cases except when the anding value is 0.

The sql version is as follows
SELECT ([tableitems].[Accesscodes] & 16) AS access FROM
[switchboard items] WHERE (((([tableitems].[Accesscodes] &
16))>0));


If I run this against an ACCESS back end the returned
value is [accesscodes] with 16 appended to the value 3116
for example. If I change the '&' to an AND I get -1 for
all rows as long as value is not 0.

Any help out there?
 
If you execute the query under ADO code (not through the interface which
uses DAO), you can use the BAND operator to perform a binary and.
 
Allen said:
If you execute the query under ADO code (not through the interface which
uses DAO), you can use the BAND operator to perform a binary and.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In Access 2002 (XP) I tried the BAND w/o using ADO - just a regular
QueryDef & it worked. But, I have the ANSI SQL-92 option checked for
this db.

SELECT [c1] Band [c2] AS Expr1
FROM junk;

c1 contains 16; c2 contains 19

Result:
16
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi3/rYechKqOuFEgEQJVSQCfVqhG4FY0cO5PFvztvwCoHEL0ZGQAoJMi
hycK6jt4Squh1IJ8YSdMBpP8
=sxh6
-----END PGP SIGNATURE-----
 
Yes, the ANSI 92 option will make a difference, as it does for wildcards.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

MGFoster said:
Allen said:
If you execute the query under ADO code (not through the interface which
uses DAO), you can use the BAND operator to perform a binary and.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In Access 2002 (XP) I tried the BAND w/o using ADO - just a regular
QueryDef & it worked. But, I have the ANSI SQL-92 option checked for
this db.

SELECT [c1] Band [c2] AS Expr1
FROM junk;

c1 contains 16; c2 contains 19

Result:
16
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi3/rYechKqOuFEgEQJVSQCfVqhG4FY0cO5PFvztvwCoHEL0ZGQAoJMi
hycK6jt4Squh1IJ8YSdMBpP8
=sxh6
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top