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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
:~) 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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
bitwise XOR calc | Luke | Worksheet Functions | 1 | September 1st, 2004 11:31 AM |