Sql Server linked table and NULL bit fields

[Michele Locati]

Hi to everybody

I've got a problem which I can't solve...

In an Access 2000 mdb I've a table linked to a Sql Server 2005 table.
The problem is that the Sql Server table contains bit fields which can
be null, but in Access I see null values as False. I found no way to
distinguish False/0 values from NULL values. The following query, for
example, returns just 1 or 2, never 0 even if there are null values in
the Sql Server table:
SELECT
IIF(field IS NULL, 0, IIF(field=0, 1, 2))
FROM
linkedTable

I've tried linking to Sql Server either {SQL Native Client} or {SQL
Server} odbc drivers, but I've got the same results...

Any hints?

Thank you a lot!
Michele

 

[Klatuu]

Bit fields in SQL Server are not exactly like Yes/No fields in Jet. They
will not return a Null value. There is really nothing you can do.

 

[Michele Locati]

Hi to everybody

I've got a problem which I can't solve...

In an Access 2000 mdb I've a table linked to a Sql Server 2005 table.
The problem is that the Sql Server table contains bit fields which can
be null, but in Access I see null values as False. I found no way to
distinguish False/0 values from NULL values. The following query, for
example, returns just 1 or 2, never 0 even if there are null values in
the Sql Server table:
SELECT
IIF(field IS NULL, 0, IIF(field=0, 1, 2))
FROM
linkedTable

I've tried linking to Sql Server either {SQL Native Client} or {SQL
Server} odbc drivers, but I've got the same results...

Any hints?

Problem solved. Here's my step for anyone interested.

My real problem was for a query like this:
INSERT INTO <local table> integerField SELECT IIF(srcField IS
NULL,NULL,IIF(srcField,1,0)) FROM <linked table>
The boolean values were copied as 0 even in the case when in the
linked table there were null values.
I solved this by executing a new query:
UPDATE <local table> INNER JOIN <linked table> ON ... SET
integerField=NULL WHERE srcField IS NULL
I really don't know why, but it works.

Ciao
Michele