Boolean AND

[Bob Quintal]

I've created a field in two different tables called
"GroupCode". It's defined as a number, field length byte.

In my Labor DB, I set the field to a value of 1.
In my Inventory DB, I set the field to a value of 2.
If it's used in both DBs, I set the field a value of 3 (1+2).
It's a binary "dip switch".

Now, when I start up my Labor DB, I set up a global variable
called glbModuleID with a 1 or a 2. That variable is defined
as a "byte" type, and it's global.

I want the RowSource of a selection box to include only rows
where the GroupCode is set to be valid for this Module (Labor,
Inventory....) So, the record is valid for Labor if the
GroupCode is 1 or 3 (the least signficant bit is set.)

I can't seem to set up the query so this works. I've been
using a where clause that says "where ((GroupCode AND
glbModuleID) = true)". This doesn't work. First, it doesn't
see glbModuleID for some reason, and second, when I force the
issue, it always returns "true" and selects the row.

What's wrong with my logic/syntax?

Use "where ((GroupCode AND glbModuleID) = glbModuleID)"

 

[JimS]

I've created a field in two different tables called "GroupCode". It's defined
as a number, field length byte.

In my Labor DB, I set the field to a value of 1.
In my Inventory DB, I set the field to a value of 2.
If it's used in both DBs, I set the field a value of 3 (1+2).
It's a binary "dip switch".

Now, when I start up my Labor DB, I set up a global variable called
glbModuleID with a 1 or a 2. That variable is defined as a "byte" type, and
it's global.

I want the RowSource of a selection box to include only rows where the
GroupCode is set to be valid for this Module (Labor, Inventory....) So, the
record is valid for Labor if the GroupCode is 1 or 3 (the least signficant
bit is set.)

I can't seem to set up the query so this works. I've been using a where
clause that says "where ((GroupCode AND glbModuleID) = true)". This doesn't
work. First, it doesn't see glbModuleID for some reason, and second, when I
force the issue, it always returns "true" and selects the row.

What's wrong with my logic/syntax?

 

[Beetle]

When you say "my Labor DB .........my Inventory DB" are you talking about two
different tables or two different databases?

 

[Bob Quintal]

Thanks Dirk... It's been ages since I've used it, and I didn't
remember that restriction.

John W. Vinson [MVP]

Interesting. In VB 1 And 2 returns 0, 1 and 3 returns 1 and 2
and 3 returns 2. In Jet SQL all three return -1

 

[Dirk Goldgar]

In

I've created a field in two different tables called "GroupCode". It's
defined as a number, field length byte.

In my Labor DB, I set the field to a value of 1.
In my Inventory DB, I set the field to a value of 2.
If it's used in both DBs, I set the field a value of 3 (1+2).
It's a binary "dip switch".

Now, when I start up my Labor DB, I set up a global variable called
glbModuleID with a 1 or a 2. That variable is defined as a "byte"
type, and it's global.

I want the RowSource of a selection box to include only rows where the
GroupCode is set to be valid for this Module (Labor, Inventory....)
So, the record is valid for Labor if the GroupCode is 1 or 3 (the
least signficant bit is set.)

I can't seem to set up the query so this works. I've been using a
where clause that says "where ((GroupCode AND glbModuleID) = true)".
This doesn't work. First, it doesn't see glbModuleID for some reason,
and second, when I force the issue, it always returns "true" and
selects the row.

What's wrong with my logic/syntax?

First, SQL can't see VBA variables. So if you want to write a query
that glbModuleID, you'll have to define it as either a function or a
property. For example,

Public Property Get glbModuleID() As Byte

glbModuleID = 1

End Property

You'll also need to use parentheses -- as in "glbModuleID()" -- after
the property name in SQL, so as to let it know that this is a value that
has to be evaluated by the VB expression service.

Second, SQL will interpret "GroupCode AND glbModuleID()" as a logical
expression that should return True if either of those values is nonzero.
You could get around that by writing a public function to force a
bitwise AND:

Public Function BAND(op1, op2) As Variant

BAND = op1 And op2

End Function

Then your query's WHERE clause could be:

WHERE BAND(GroupCode, glbModuleID()) <> 0

 

[John W. Vinson]

I can't seem to set up the query so this works. I've been using a where
clause that says "where ((GroupCode AND glbModuleID) = true)". This doesn't
work. First, it doesn't see glbModuleID for some reason, and second, when I
force the issue, it always returns "true" and selects the row.

VBA variables are not available to the SQL query engine. You'll need to write
a little VBA function to return the variable instead.

Also, the AND operator is *NOT* a binary operator. Any nonzero value is
considered TRUE and only a zero value is FALSE, so 2 AND 3 returns -1 (that
is, true, because both values are nonzero). You can use BAND instead (binary
AND).

John W. Vinson [MVP]

 

[Dirk Goldgar]

In

You can use BAND instead (binary AND).

I think you can only do that if you have set the database option to use
SQL Server Compatible (ANSI 92) Syntax, or if you execute the query via
ADO. I don't think the BAND operator is available using the older,
default Jet SQL syntax.

 

[John W. Vinson]

In

I think you can only do that if you have set the database option to use
SQL Server Compatible (ANSI 92) Syntax, or if you execute the query via
ADO. I don't think the BAND operator is available using the older,
default Jet SQL syntax.

Thanks Dirk... It's been ages since I've used it, and I didn't remember that
restriction.

John W. Vinson [MVP]

 

[JimS]

I really appreciate all your advice, guys. I can always count on you folks.
Yes, two different databases using linked tables that are often shared. Labor
and Inventory share several tables, mostly reference tables. I'm planning
ultimately to combine them all into one back end database, but my client
didn't understand why I'd do that, so I mollified for now. The third database
("financial") wil be the key to that.

So, I'm using Access 2003. I'm guessing "BAND" is not a valid operator. I'll
do a function like you said. It's quick, easy, and replicatable.

Is my data type sensible here? Should I simplify it some way?

 

[John W. Vinson]

Interesting. In VB 1 And 2 returns 0, 1 and 3 returns 1 and 2
and 3 returns 2. In Jet SQL all three return -1

Exactly. SQL and VB are different languages.

Exactamente. SQL y VB son lenguas distinctas.

John W. Vinson [MVP]

 

[JimS]

How do you say "cute" in Spanish and sound sarcastic?