Logical Operations in a report SQL

[Jael]

Background:
I have a database that records responses to a survey.
Most questions have multiple responses.
The responses are True or False (with False the default) only.
More than one response can be selected True for each question.
There are 20+ questions with responses ranging from 3 to 44.

Sample Question:
Q10 What program languages do you use:
1. VB
2. C++
3. Forth
4. Assembly
5. Other
I use a single numeric entry in my data table for each question and code the
responses as follows:
Question 10 has 5 possible answers. The table entry is called Q10 and data
would be bit coded as:
If Q10Ans1 is True then Q10 = Q10 OR 2^0
If Q10Ans2 is True then Q10 = Q10 OR 2^1
If Q10Ans3 is True then Q10 = Q10 OR 2^2
If Q10Ans4 is True then Q10 = Q10 OR 2^3
If Q10Ans5 is True then Q10 = Q10 OR 2^4
This “sets†the individual bit positions in Q10 based on the response.
E.g, if Q10 =3 then response 1 and 2 (bit positions 0 and 1) were selected.

To clear the response-bit, I replace the OR with XOR.
To test each response I use (Q10 and 2^n)<>0. Where n = bit position (0 – 4)
or response (1 – 5)

My question is:
When I build a report, I use VBA to populate the detail and that works fine
but I’d like to do logical comparisons in the SQL builder using AND, XOR or
OR but them seem not to produce the results I need.
That is, if I have a filter value = IIF((Q10 AND 1)<>0,â€Trueâ€,â€Falseâ€). I
get a True whenever Q10 has any value and False when Q10 is empty. Is this a
function of the “IFF†operator or are logical operations not valid. Or am I
just coding it wrong?

Thanks,
Jael

 

[Allen Browne]

Before JET 4, there were no binary operators built into the Access SQL. From
Access 2000 on, it does support BAND (binary AND), BOR (binary OR), etc.

However, Microsoft only partially implemented this, so you cannot use these
in the graphical query interface, nor in DAO code. It works only in ADO.

It is possible to code your own VBA functions to handle the bit-wise
operations, and to use these functions in a query or directly in the
ControlSource of a control. But the more recent versions of Access tend to
be considerably slower at executing queries that contain user-defined VBA
functions.

 

[Jael]

Ahhha! Thanks Allen. That explains why my reports are so slow. Guess my
background in Assembly Language lead me down a less traveled path. I didn't
want to make a large table with each question-response a unique table entry,
but that may be a more report friendly way to go. I can automatically
generate a new table from the existing table (saving data re-entry time -
besides it's more fun to program it than to enter it). There's only about 225
entry points.

BTW:
For the binary operators, I used AND, OR, ... You cited BAND, BOR, ... are
they interchangable?

Thanks again,
Jael

 

[Allen Browne]

AND and OR are interchangeable as logical and binary operators in VBA code
(i.e. VBA decides which operator is intended depending on the context), but
I would not expect them to be be interchangeable in SQL clauses.

 

[Jael]

OK! That makes sense. I would expect less decision intelligence in a SQL
clause interpreter versus a VBA compilation.
Thanks Allen,
Jeff -
Jael Wms
Coachella Valley, California
Hot but it's a dry heat!