Join on an Empty Field

  • Thread starter Thread starter Commish
  • Start date Start date
C

Commish

I am trying to use Access to confirm that input files are being
properly imported and processed by a third party app - testing the
developer's work.

And I am using access to join the tables that represent that input and
output. Anyway, when I match on a given column of data that contains a
mix of values like "0318" and empty cells (nulls), the query only
joins and matches on the data that is present.

For the purpose of a join, does Null equal Null? If the value is null
in both tables, will that be a successful join?
 
Without testing, I would expect this kind of thing to work:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON ((Table1.Field1 = Table2.Field1)
OR ((Table1.Field1 Is Null) AND (Table2.Field1 Is Null)));

Access probably won't be able to show that in design view.
Watch the bracketing when mixing ANDs and ORs.
The expression above should be more efficent than Nz().
 
Without testing, I would expect this kind of thing to work:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON ((Table1.Field1 = Table2.Field1)
OR ((Table1.Field1 Is Null) AND (Table2.Field1 Is Null)));

Access probably won't be able to show that in design view.
Watch the bracketing when mixing ANDs and ORs.
The expression above should be more efficent than Nz().
Click to expand...

Thanks, this is useful and I've made it work. There is a way to make
it work in the design view - it's amost like an easter egg - you make
the SQL work and then if successful it is displayed in Design View.
 
Back
Top