ACC: Criterion Returns Inconsistent Results in SQL BIT Field

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you run a query in Microsoft Access against a linked (attached) SQL Server table, you may encounter unexpected results if both of the following conditions are met:

• The linked table contains a field of type BIT.
• The criterion of the BIT field is set to -1.

CAUSE

When a linked SQL Server table contains a field of type BIT, the field appears as a Yes/No field in Microsoft Access. However, unlike Microsoft Access, an SQL BIT field can contain only the values 0 and 1, whereas a Microsoft Access Yes/No field can contain the values 0 and -1. When you use a value of -1 in the BIT field's criterion, the resulting recordset will contain no records.

RESOLUTION

Do not use -1 as the criterion for a SQL BIT field in Microsoft Access, because the SQL Server will look for that literal value in the attached table. Instead, use either the value 1 or the intrinsic constant True in the SQL BIT field's criterion.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a SQL Server table with a field with the BIT type.

2. In Microsoft Access, link to the table you created in step 1.

3. Create a query based on the linked table, and use the value -1 for the BIT field's criterion.

4. Run the query.