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")

 

[Jamie Collins]

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

Note Doug's proposed solution may give unexpected results with an OUTER
JOIN.

The OP's situation is an example of bad SQL DDL (schema design) making
SQL DML (queries) harder to write.

If a 'blank' address line is supposed to be comparable with a 'blank'
line in another address then allowing a NULL value is not appropriate.
The column(s) should be declared as NOT NULL with the (common)
placeholder value (Doug's 'X' but I prefer the more readable '{{NA}}')
declared as the default; there should also be a validation rule to
disallow zero-length strings.

Getting the database designer to fix the table(s) means the application
developers will not have to handle the design flaw in *every* query
they write.

Jamie.

--