Microsoft KB Archive/117535

= 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.



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 kbprb KB117535

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.