Can someone explain the difference between "AND" and "&" in Access

Question

I'm pretty darn new to Access developement. Yesterday, I developed a query and in the WHERE portion, I used "&" as in :

WHERE ((condition A Is Null) & (condition B Is Null))

It returned 53 hits.

Then I changed it to

WHERE ((condition A Is Null) AND (condition B Is Null))

and got 71 hits.

I know it has to do with 'bitwise and' vs 'logical and', but can someone take a moment to explain this to me in noobish (English)?

What version of MS Access is this? When I try, I get the result of concatenation, i.e., `-1-1`, or `-10`, because `&` is the string concatenation operator. You should just always use `AND`. — mellamokb, Jul 05 '12 at 15:35
Access 07. I ran a query to match credit card numbers using only the last 4 numbers. Have to do a join to see if the card's last numbers and the employee number don't exist, then I know it's a new card to add to the DB. — mrwienerdog, Jul 05 '12 at 15:57
As @mellamokb said, you want AND. The & is the VBA/Jet/ACE concatenator. Note also WHERE, not WHILE. — Fionnuala, Jul 05 '12 at 16:00
See http://stackoverflow.com/questions/10197652/sql-if-column-a-is-null-then-use-column-bs-value-to-compute-column-c noting in particular the comments. — Fionnuala, Jul 05 '12 at 16:05

score 3 · Accepted Answer · answered Jul 05 '12 at 18:44

AND is a logical operator. & is a concatenation operator. Review Table of operators for more details about the various operators available in Access 2007.

To see the differences in action, start with this as table WeinerDog.

id field1 field2
 1 a
 2        y
 3

The blanks in the field1 and field2 columns represent Null values.

Next run this query:

SELECT
    WeinerDog.id,
    WeinerDog.field1,
    WeinerDog.field2,
    (WeinerDog.field1 Is Null)
        & (WeinerDog.field2 Is Null) AS concat_result
FROM WeinerDog
WHERE
    (WeinerDog.field1 Is Null) & (WeinerDog.field2 Is Null);

That query should give you this result set:

id field1 field2 concat_result
 1 a             0-1
 2        y      -10
 3               -1-1

Examine the concat_result column. It contains strings which are the truth values of the 2 expressions concatenated together. Those truth values are either True (-1) or False (0). However, since the result of concatenation must be a string, the numeric truth values are first cast as strings before they are joined together.

Then examine the WHERE clause. The db engine will give you all rows for which the WHERE clause evaluates as True. And actually, not just -1, but any non-Null value other than zero can stand in for True. As you can see from the concat_result column, none of those rows will be evaluated as zero (False) by the WHERE clause ... so all rows in the table will be included in the query result set.

If you change the WHERE clause to substitute AND for &, the query will return only the WeinerDog row (id=3) which has Null for both field1 and field2.

WHERE
    (WeinerDog.field1 Is Null) AND (WeinerDog.field2 Is Null);

Can someone explain the difference between "AND" and "&" in Access

1 Answers1