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.

Additional query words: odbc sql bit

Keywords          : kbinterop OdbcSqlms 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: April 6, 1999