ACC: Criterion Returns Inconsistent Results in SQL BIT Field (117535)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
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.

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.
Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbinterop kbprb KB117535
©2002 Microsoft Corporation. All rights reserved.