join two tables with Blank fields

[jj]

Hi Ng

I got two tables with adresses - With street name, postal number, house
number, Level (If there is more levels), Letter (if there is letters).

Now I would like to join these two table (so I make a join between postal
number, street, housenumber, level, Letter - But I only finds the records
with a value in each field!! - If there is a blank in for example Level -
then this record will not show up.

Can anyone tell me how I can get these records too?

TIA
JJ

 

[Douglas J. Steele]

Sounds as though they're Null (as opposed to blank).

Go into the SQL associated with your query.

Where you've currently got something like

ON Table1.Field1 = Table2.Field1

, you need to change it to


ON (Table1.Field1 = Table2.Field1) OR (Table1.Field1 IS NULL AND
Table2.Field1 IS NULL)

Alternatively, you could use:

ON Nz(Table1.Field1, X) = Nz(Table2.Field1, X)

where X is some value that you know does not appear as a value for Field1.
If Field1 is a text field, make sure you've got quotes around it:

ON Nz(Table1.Field1, "X") = Nz(Table2.Field1, "X")

 

[jj]

Thx a lot Douglas

Sounds as though they're Null (as opposed to blank).

Go into the SQL associated with your query.

Where you've currently got something like

ON Table1.Field1 = Table2.Field1

, you need to change it to


ON (Table1.Field1 = Table2.Field1) OR (Table1.Field1 IS NULL AND
Table2.Field1 IS NULL)

Alternatively, you could use:

ON Nz(Table1.Field1, X) = Nz(Table2.Field1, X)

where X is some value that you know does not appear as a value for Field1.
If Field1 is a text field, make sure you've got quotes around it:

ON Nz(Table1.Field1, "X") = Nz(Table2.Field1, "X")