Stephen Hait
shait at mindspring.com
Mon Feb 9 18:17:53 CST 2004
As far as I know this difference in how True is represented in
TrueFalse/bit fields has existed between Access and SQL Server
since at least SQL Server 6.5. Access uses -1 for True, 0 for
False while SQL Server uses 1 for True and 0 for False.
One approach that allows you to work the same way with both
systems is to only check against a False condition. Rather than
test for -1 or 1 to test for True, just test for <> 0 instead.
Stephen
> We upgraded our SQL Server 7.0 backend to SQL Server 2000 over the
> weekend. The front end runs Access 2000 GUI client applications.
>
> After the conversion, we are experiencing a problem with Access
> queries that select data from the [converted] back end SQL Server
> tables. The problem involves a hardcoded "-1" (i.e. "True" logic
> condition criteria) in the Access queries testing against a "Bit"
> data type field in the SQL Server back end. With the SQL Server 7.0
> backend tables, the Access queries were working just fine. In
> converting to SQL Server 2000, it appears that previously populated
> bit fields populated with the value of "-1" ("True") got
> arbitrarily switched to a value of "1" during the conversion. (The
> "-1" values in the back end table ["Bit" fields] got changed, NOT
> the "-1" criteria value in the front end query cells!) Further,
> when the query runs against the [converted] back end table, the "-1"
> criteria (from the Access query) is no longer recognized - the query
> doesn't return data.
>
> Needless to say, we are no longer getting the expected results (from
> execution of the queries) after the conversion from SQL Server 7.0
> to SQL Server 2000. I suspect that Microsoft implemented a change
> in the behaviour of "Bit" fields from version 7.0 to SQL Server
> 2000. While I search the Knowledge Base and try to track this down,
> can anybody confirm whether this is the case?
>
> We have a lot of queries (and VBA code) that test the "-1" value
> against fields of data type "Bit", so we don't want to start
> changing these hardcoded criteria (from "-1" to just plain "1")
> until and unless we are ABSOLUTELY SURE that this is a SQL Server
> version 7.0 to version 2000 conversion anomaly.
>
> TIA for any info or enlightenment on this problem.
>
>
> Alan C. Lawhon
>
>
>
>
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com