How to determine NULL values in a linked table in Access 1

[Pack10]

I will be building a questionnaire whose data will be stored in SQL Server.
Regarding Yes/No questions, last year in a similar project, I stored them as bit fields.
When I linked the table to an Access database for reporting and analysis purposes, the questions that
were not answered (NULL in SQL Server) came across as No in access. In access queries, if I
had a column ...IIf(Column = -1,"Yes","No") that worked fine, but how would I get the ones that
were NULL. In Access, they don't look NULL in the table.

 

[Olaf Doschke]

ODBC does an automatch of field types and access yes/no fields don't support NULL. There is nothing you can do about this via linked tables.
You have to write a query with COALESCE (yesno, 2), that would result in an int being 0 for false(no), 1 for true(yes) and 2 for NULL.

Bye, Olaf.

 

[Pack10]

Hi
I am working with a test table in SQL Server with 5 records. I have a bit field, with a field named "Answer"
the values are True, True, NULL, NULL, False. When I link to this table in Access, the values are -1,-1,0,0,0.
The SQL Server NULLS 0 in Access. Do I have to handle this on the SQL side of things with the COALESE function.
I think that is what your earlier post described.

Thanks - just want to be clear.

 

[Pack10]

Hi
Got it, thanks. I will now be able to report more accurate results this time around.

 

[gk53]

Use isnull function
isnull(field, devaultValue)