ansinull and comparing null fields

[Guest]

I am trying to compare two null fields in two tables.

for example:

SELECT table1.*
FROM table1, table2
WHERE table1.nullColumn= table2.nullcolumn

This returns zero records because a value of "unknown" is returned by the
WHERE clause. In SQL the 'SET ANSINULL OFF' command would fix this problem,
but Access does not accept the 'SET ANSINULL OFF' command? Any other ideas?

 

[Brendan Reynolds]

WHERE NZ(table1.nullColumn, "") = NZ(table2.nullColumn, "") (or NZ(column,0)
for numeric columns)

.... which is Access-specific, or ...

WHERE (table1.nullColumn = table2.nullColumn) OR (table1.nullColumn IS NULL
AND table2.nullColumn IS NULL)

.... which is more verbose, but generic.