A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Bitwise And



 
 
Thread Tools Display Modes
  #11  
Old October 3rd, 2004, 11:50 PM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

Hi Tom

Yes, I referred to "AND" in Queries / SQL. My previous post started with
"In JET, ...".

When you tested the "function" (as or similar to what Sam posted), you
tested the VBA "AND", not the JET "AND".

Test with the following SQL:

SELECT (4 And 4) As Test
FROM Table1

where Table1 is any Table with Records. You should get a whole column of -1
(True, i.e. logical operator) and not 4 (If JET And were bitwise operator).

--
Cheers
Van T. Dinh
MVP (Access)


"Tom Ellison" wrote in message
...
Dear Van:

I had to check out my facts and also test the function thoroughly
before I began responding here.

From online help:

The And operator also performs a bitwise comparison of identically
positioned bits in two numeric expressions and sets the corresponding
bit in result

This is the case for VBA, and for the use of And in a function there.
It tests out correctly there.

Perhaps you were referring to the use of And in a query. Could its
use be different there? I wouldn't think so, but haven't tested it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




  #12  
Old October 4th, 2004, 12:22 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

I am not sure where I read it from but BAND is definitely in JET 4. Note
that my previous post mentioned that it may not work though the Query
interface or the code using DAO.

I did a quick little test code (using ADO, of course) in A2002:

****Code starts****
Public Sub Test_BAND()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT (4 BAND 4) As Test FROM Table1"

rs.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

With rs
While (rs.EOF) = False
Debug.Print rs.Fields("Test").Value
rs.MoveNext
Wend
End With

rs.Close
Set rs = Nothing
Set cnn = Nothing

End Sub
****Code ends****

"Table1" can be any Table with some Records. When tested, it worked
correctly giving me a column of 4 (i.e. bitwise op) in the Debug window. If
I replace "BAND" with "AND" in the SQL String, I get a column of -1 (True,
i.e. logical op)!

In fact, in the SQL String, you don't even need the FROM clause and the SQL
String will give only one row (and only one column).

--
HTH
Van T. Dinh
MVP (Access)




"Sam Hobbs" wrote in message
...
"Van T. Dinh" wrote in message
...
In JET, I am fairly sure the "AND" is a purely logical operator. "BAND"

is
a
bitwise AND operator introduced in JET 4 but there may be problems using
it
in the Query interface. I am fairly sure BAND works in ADO code but you
will have to try it out.


Access says that BAnd is not valid as an operator and I can't find BAnd in
the list of functions for queries. However it probably helps a lot for me

to
realize that I should be able to use whatever works in SQL, so that is

what
I should look for. I should be able to get an answer relatively easily by
looking in forums and such for the SQL way to do this.

Note that in your result, -1 means True but this doesn't mean that it is

a
SystemObject since in Query, and expression like 1 AND 2 will

returns -1.
The reason is that any non-zero number is interpreted as True and only 0
is
interprested as False. So the above expression will be imterpreted as
True
AND True which results in True, i.e. -1.


Yes, I understand. That is how C/C++ works; at least, for the C/C++

logical
(boolean) operator "&&", 0 is false and anything that is not 0 is true.




  #13  
Old October 4th, 2004, 01:48 AM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

Thank you.

Yes, the Test_BAND procedure works as you say. However if I copy the SQL and
paste it into an Access query, Access complains.

I found something about SQL Server somewhere that says that BITAND is the
equivalent of the "&" operator, but neither of those work for an Access
query. At least I was not able to get them to work; the "&" operator always
concatenated text values.

Access queries do seem to do their own thing.


"Van T. Dinh" wrote in message
...

"Table1" can be any Table with some Records. When tested, it worked
correctly giving me a column of 4 (i.e. bitwise op) in the Debug window.
If
I replace "BAND" with "AND" in the SQL String, I get a column of -1 (True,
i.e. logical op)!



  #14  
Old October 4th, 2004, 02:48 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

Sam

You are not reading my post fully. I wrote *twice* previously that there
are problems using BAND in the Access Query interface or DAO code!

I think the reason is that Access Query interface was carried over from A97
which was designed for JET 3.5 and BAND was introduced in JET 4. Note that
JET and Access are 2 separate and distinct components (a third major comp is
VBA/Access VBA) and Microsoft would have had to do a fair bit of work to get
them to work together. I number of new elements in JET 4 SQL syntax were
somehow not incorporated into the Access Query interface.

--
HTH
Van T. Dinh
MVP (Access)




"Sam Hobbs" wrote in message
...
Thank you.

Yes, the Test_BAND procedure works as you say. However if I copy the SQL

and
paste it into an Access query, Access complains.

I found something about SQL Server somewhere that says that BITAND is the
equivalent of the "&" operator, but neither of those work for an Access
query. At least I was not able to get them to work; the "&" operator

always
concatenated text values.

Access queries do seem to do their own thing.




  #15  
Old October 4th, 2004, 06:28 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

FWIW, when using 'ANSI' mode, you can use 'BAND'
in queries: (2 BAND 4)

(david)

"Sam Hobbs" wrote in message
...
I have the following in a module that works:


Const DB_SYSTEMOBJECT = &H80000002

Public Function IsSystemObject(Flags)
IsSystemObject = (Flags And DB_SYSTEMOBJECT)
End Function


However when I use "Flags And 2147483650" in a query, I don't get the same
results as I do when I say "IsSystemObject(Flags)". Is a way to do the
equivalent of the IsSystemObject Function except in a query directly?

I guess the problem is that the And operator has dual purposes; it is

either
a logical or a bitwise operator. So I can probably figure out a way to get
it to be a bitwise operator, but if someone does not mind enlightening me
with their experience, then that will problably help.

Note: Some people will recognize that this is testing the Flags field of

the
MSysObjects table to determine if the object is a system object.





  #16  
Old October 4th, 2004, 04:08 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

I am sorry I misunderstood. However I have read the posts again and I still
don't understand.

Originally you said .... "but there may be problems using it in the Query
interface" .... "but you will have to try it out". Then you said "Note that
my previous post mentioned that it may not work though the Query interface
or the code using DAO.".

So I thought you were saying you were not sure so I was reporting my
results. I get frustrated when people don't provide feedback to my messages
when I try to help, so I was just doing for you what I would want others to
do for me.


"Van T. Dinh" wrote in message
...
Sam

You are not reading my post fully. I wrote *twice* previously that there
are problems using BAND in the Access Query interface or DAO code!



  #17  
Old October 4th, 2004, 04:52 PM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default

OK, just a bit of misunderstanding (on my part) as I didn't expect feedback
of testing with the Query interface.

What I meant in previous posts is that I had problems using BAND through the
Query interface and DAO. However, there are a lot of smart people around
and they may prove that I am wrong and there are ways to use BAND through
the Query interface or DAO (which I haven't found yet) and therefore I wrote
"There may be problems ...). You may even prove me wrong later!

When I wrote "try it out", I meant trying in ADO code and I subsequently
posted ADO code in a later post. Hence, I didn't expect you to test with
the Query interface or DAO code.

--
Cheers
Van T. Dinh
MVP (Access)




"Sam Hobbs" wrote in message
...
I am sorry I misunderstood. However I have read the posts again and I

still
don't understand.

Originally you said .... "but there may be problems using it in the Query
interface" .... "but you will have to try it out". Then you said "Note

that
my previous post mentioned that it may not work though the Query interface
or the code using DAO.".

So I thought you were saying you were not sure so I was reporting my
results. I get frustrated when people don't provide feedback to my

messages
when I try to help, so I was just doing for you what I would want others

to
do for me.




  #18  
Old October 4th, 2004, 05:24 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

Thank you. That helps; I assume it answers the question of whether BAnd
works in a query.

As for FWIW, I don't know what it is and I can't find it in the Access
documentation or MSDN.

As for ANSI SQL query mode, the documentation says that it applies to an
entire database. If so, then unfortunately it is not useful for this. This
is a general-purpose solution for use in many databases; that is, it needs
to work with the most common Access databases.


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
FWIW, when using 'ANSI' mode, you can use 'BAND'
in queries: (2 BAND 4)



  #19  
Old October 5th, 2004, 12:37 AM
david epsom dot com dot au
external usenet poster
 
Posts: n/a
Default

:~) For What It's Worth

In your case, worth only a penny I guess :~)

Note: it is possible, using ADO, to create
ANSI queries in a Non-Ansi database. In Access
2000 it is not possible to put the database
into ANSI mode, and any ADO-created ANSI
querydefs are not visible in the Access
database window. But they are still there, and
may be enumerated.

AFAIK (As Far As I Know :~), no-one has ever
discussed the behaviour in Access of ANSI queries
in a (non-ANSI) A2000 database, other than
to say that they are not visible if they exist.

Access/Jet querydefs have two streams: Text
(SQL), and compiled (binary). Both ANSI and
non-ANSI SQL will compile, and the compiled
query is what is used, not the actual Text
stream. So you would expect that compiled
ANSI querydefs might be used interchangeably
with compiled non-ANSI querydefs, but I've
never tried it.

If you create an ADO connection to an A2K database,
and use the connection to create a query, you
apparently get an ANSI query. I know this because
there used to be questions here like: "I used
ADO to create a query in my database. It's there,
but I can't see it in the Access database window.
Why not?"

(david)

"Sam Hobbs" wrote in message
...
Thank you. That helps; I assume it answers the question of whether BAnd
works in a query.

As for FWIW, I don't know what it is and I can't find it in the Access
documentation or MSDN.

As for ANSI SQL query mode, the documentation says that it applies to an
entire database. If so, then unfortunately it is not useful for this. This
is a general-purpose solution for use in many databases; that is, it needs
to work with the most common Access databases.


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
FWIW, when using 'ANSI' mode, you can use 'BAND'
in queries: (2 BAND 4)





  #20  
Old October 5th, 2004, 02:46 PM
Sam Hobbs
external usenet poster
 
Posts: n/a
Default

Oh, I should have recognized "FWIW" but I did not. My mind was sure blank
but now it seems very obvious.

Thank you for this too. I don't know how to create queries using ADO. I
assume I can figure that out and I will eventually. Perhaps the meaning of
what you are saying is also obvious and I will realize it later. It does
sound interesting and useful. I am certainly interested in knowing what an
ANSI query looks like in the MSysObjects table. When you say "create an ADO
connection to an A2K database, and use the connection to create a query", I
assume that creation is not the same thing as executing. I assume you mean
that creation is done once and then the query can be executed without ADO,
but I don't know how.



"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
:~) For What It's Worth

In your case, worth only a penny I guess :~)

Note: it is possible, using ADO, to create
ANSI queries in a Non-Ansi database. In Access
2000 it is not possible to put the database
into ANSI mode, and any ADO-created ANSI
querydefs are not visible in the Access
database window. But they are still there, and
may be enumerated.

AFAIK (As Far As I Know :~), no-one has ever
discussed the behaviour in Access of ANSI queries
in a (non-ANSI) A2000 database, other than
to say that they are not visible if they exist.

Access/Jet querydefs have two streams: Text
(SQL), and compiled (binary). Both ANSI and
non-ANSI SQL will compile, and the compiled
query is what is used, not the actual Text
stream. So you would expect that compiled
ANSI querydefs might be used interchangeably
with compiled non-ANSI querydefs, but I've
never tried it.

If you create an ADO connection to an A2K database,
and use the connection to create a query, you
apparently get an ANSI query. I know this because
there used to be questions here like: "I used
ADO to create a query in my database. It's there,
but I can't see it in the Access database window.
Why not?"

(david)

"Sam Hobbs" wrote in message
...
Thank you. That helps; I assume it answers the question of whether BAnd
works in a query.

As for FWIW, I don't know what it is and I can't find it in the Access
documentation or MSDN.

As for ANSI SQL query mode, the documentation says that it applies to an
entire database. If so, then unfortunately it is not useful for this.
This
is a general-purpose solution for use in many databases; that is, it
needs
to work with the most common Access databases.


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
FWIW, when using 'ANSI' mode, you can use 'BAND'
in queries: (2 BAND 4)







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
bitwise XOR calc Luke Worksheet Functions 1 September 1st, 2004 11:31 AM


All times are GMT +1. The time now is 10:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.