ACC: Criterion Returns Inconsistent Results in SQL BIT Field
Article ID: 117535
Article Last Modified on 1/19/2007
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q117535
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.
Additional query words: odbc sql bit
Keywords: kbinterop kbprb KB117535