By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Boolean Expression in Query Access vs vb.net (Please Help!)

P: n/a
Hi!

I'm trying to create a query with a boolean expression like this:

select (4 and 1) as Value from Table1

this query return always -1, but when I make the same calculation in
visual basic, the value returned is 0.

Can anyone tell me why the expression (4 and 1) return different value
between access and vb.net?, how can I get the same value in both
programs?

Thanks in advance.
Bye.

Sep 1 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On 1 Sep 2006 12:48:53 -0700, "rguarnieri" <ru***@hmstrategies.com>
wrote:
>Hi!

I'm trying to create a query with a boolean expression like this:

select (4 and 1) as Value from Table1

this query return always -1, but when I make the same calculation in
visual basic, the value returned is 0.

Can anyone tell me why the expression (4 and 1) return different value
between access and vb.net?, how can I get the same value in both
programs?

Thanks in advance.
Bye.
Regrettably, I don't have a solution. I recently ran into the
identical issue. I couldn't find a way to force a bitwise comparison
in a query.

It is doing a logical AND, not a numerical one. 4 is true and 1 is
true so the result is true. If you were to change it to (4 and 0) it
would return 0, because 0 is false. I spent hours trying to find a
way to force a bitwise comparison but finally gave up and wrote a
function to give me the result I needed.

-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com
Sep 1 '06 #2

P: n/a
On 1 Sep 2006 12:48:53 -0700, "rguarnieri" <ru***@hmstrategies.comwrote:
>Hi!

I'm trying to create a query with a boolean expression like this:

select (4 and 1) as Value from Table1

this query return always -1, but when I make the same calculation in
visual basic, the value returned is 0.

Can anyone tell me why the expression (4 and 1) return different value
between access and vb.net?, how can I get the same value in both
programs?

Thanks in advance.
Bye.
The Not, And and Or operators work differently in VB.NET. In VB6 and earlier, these functions
performed either logical or Bitwise operations depending on the context they were used in. If all
their operands were of type boolean they performed logical operations. Otherwise they performed
bitwise operations.

Now they will only perform logical operations. VB.NET introduces the BitAnd, BitOr, BitNot and
BitXor keywords to perform bitwise operations.

If your existing VB6 code uses the logical operators on non-boolean values either you will get
incorrect results or your code will convert to use the slower VB6.And, VB6.Or and VB6.Not
compatibility functions.

(quoted from http://www.thescarms.com/vbasic/VB6vsVBNet.asp)

Sep 1 '06 #3

P: n/a

Thank you for your answer!, I also thought about using a function but I
can't use an access function in a query that I will use from vb.net.
That's my problem :(

Any idea of replacing the expression for something that works?
Thank you.
Good week end!

Randy Harris wrote:
On 1 Sep 2006 12:48:53 -0700, "rguarnieri" <ru***@hmstrategies.com>
wrote:
Hi!

I'm trying to create a query with a boolean expression like this:

select (4 and 1) as Value from Table1

this query return always -1, but when I make the same calculation in
visual basic, the value returned is 0.

Can anyone tell me why the expression (4 and 1) return different value
between access and vb.net?, how can I get the same value in both
programs?

Thanks in advance.
Bye.

Regrettably, I don't have a solution. I recently ran into the
identical issue. I couldn't find a way to force a bitwise comparison
in a query.

It is doing a logical AND, not a numerical one. 4 is true and 1 is
true so the result is true. If you were to change it to (4 and 0) it
would return 0, because 0 is false. I spent hours trying to find a
way to force a bitwise comparison but finally gave up and wrote a
function to give me the result I needed.

-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com
Sep 1 '06 #4

P: n/a
"rguarnieri" <ru***@hmstrategies.comwrote in
news:11**********************@e3g2000cwe.googlegro ups.com:
Any idea of replacing the expression for something that works?
I can't conceive of what you're trying to accomplish. Are you
wanting to do a Boolean evaluation of field values, or are you, for
instance, appending based on values gotten from elsewhere?

Why not compare each value to 0? That is, (4 <0 And 1 <0) should
return True no matter what, bitwise or not.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 1 '06 #5

P: n/a
rguarnieri wrote:
Thank you for your answer!, I also thought about using a function but I
can't use an access function in a query that I will use from vb.net.
That's my problem :(

Any idea of replacing the expression for something that works?
How about:

SELECT (4 BAND 1) AS foo

Thank you.
Good week end!

Randy Harris wrote:
On 1 Sep 2006 12:48:53 -0700, "rguarnieri" <ru***@hmstrategies.com>
wrote:
>Hi!
>
>I'm trying to create a query with a boolean expression like this:
>
>select (4 and 1) as Value from Table1
>
>this query return always -1, but when I make the same calculation in
>visual basic, the value returned is 0.
>
>Can anyone tell me why the expression (4 and 1) return different value
>between access and vb.net?, how can I get the same value in both
>programs?
>
>Thanks in advance.
>Bye.
Regrettably, I don't have a solution. I recently ran into the
identical issue. I couldn't find a way to force a bitwise comparison
in a query.

It is doing a logical AND, not a numerical one. 4 is true and 1 is
true so the result is true. If you were to change it to (4 and 0) it
would return 0, because 0 is false. I spent hours trying to find a
way to force a bitwise comparison but finally gave up and wrote a
function to give me the result I needed.

-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com
Sep 3 '06 #6

P: n/a
SELECT (4 BAND 1) AS foo

is not an expression accepted by Access, I'm using Access 2000, any
other idea?
Thanks.!

Gord wrote:
rguarnieri wrote:
Thank you for your answer!, I also thought about using a function but I
can't use an access function in a query that I will use from vb.net.
That's my problem :(

Any idea of replacing the expression for something that works?

How about:

SELECT (4 BAND 1) AS foo

Thank you.
Good week end!

Randy Harris wrote:
On 1 Sep 2006 12:48:53 -0700, "rguarnieri" <ru***@hmstrategies.com>
wrote:
>
Hi!

I'm trying to create a query with a boolean expression like this:

select (4 and 1) as Value from Table1

this query return always -1, but when I make the same calculation in
visual basic, the value returned is 0.

Can anyone tell me why the expression (4 and 1) return different value
between access and vb.net?, how can I get the same value in both
programs?

Thanks in advance.
Bye.
>
Regrettably, I don't have a solution. I recently ran into the
identical issue. I couldn't find a way to force a bitwise comparison
in a query.
>
It is doing a logical AND, not a numerical one. 4 is true and 1 is
true so the result is true. If you were to change it to (4 and 0) it
would return 0, because 0 is false. I spent hours trying to find a
way to force a bitwise comparison but finally gave up and wrote a
function to give me the result I needed.
>
-=-=-=-=-=-=-=-=-=-=-=-=
Randy Harris
tech at promail dot com
Sep 5 '06 #7

P: n/a
"rguarnieri" <ru***@hmstrategies.comwrote in message
<11**********************@b28g2000cwb.googlegroups .com>:
SELECT (4 BAND 1) AS foo

is not an expression accepted by Access, I'm using Access 2000, any
other idea?
Thanks.!
Not accepted by Access - for the 2000 version, it does hold true -
but they are features of Jet 4.0, which Access uses, so they are
accessible - with some limitations, though, also from Access 2000.

In Access versions later than 2000, you can switch to ANSI-92 SQL
mode, where Access queries also support bitwise BAND and BOR.

NOTE - I'd be a bit carefull switching mode in existing dbs in 2002+
versions, too, see for instance
http://office.microsoft.com/en-us/as...704831033.aspx
with some information about the mode, and some possible consequences
of changing.

You can have the 2000 version also accept this, by creating the
queries through ADO, but I fear those queries won't be visible
or accessible through the Access interface (in the 2000 version), but
you can reach them through ADO - small air code sample

dim cn as adodb.connection
dim rs as adodb.recordset

set cn = currentproject.connection

cn.execute "CREATE PROC myBitWiseTest As (" & _
"SELECT field1, field2, MyNum BAND 2 As MyBitWise " & _
"FROM sometable)"
set rs = cn.execute("myBitWiseTest",, adcmdstoredproc)
debug.print rs.getstring

Another way, would be to use dynamic SQL, where both ADO and ADO.Net
with the OLE DB provider, should give the same result on

"SELECT field1, field2, MyNum BAND 2 As MyBitWise FROM sometable"

So - I fear that with Access 2000, you will not be able to utilize
any of this directly in the UI (recordsource, rowsource...), but you
should be able to use it through ADO, and excpect the same result
both in Access and when connecting to the mdb file through VB.Net.
You can bind Access form recordsets to ADO recordsets also in the
2000 version, but with some hassle (lack of ability to update, for
one), see
http://support.microsoft.com/default...b;EN-US;227053

--
Roy-Vidar
Sep 5 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.