Join Queries Involving a blank field

[JornyO]

I need to run a join query looking for all entries in Table B that correspond
to the same address is Table A. The common fields are StreetNumber,
StreetName and StreetType. It is a join showing ALL records in Table A, and
only those in Table with a match on all three fields (i.e. I want to know if
there is no corresponding record in Table B). The query works perfectly
except in one case, where the StreetType is blank. This happens often
because although normally a StreetType will be AVE, STREET, ROAD, etc...,
sometimes a street has no designation (i.e. Broadway is just Broadway, not
Broadway Ave or Broadway Road). Note that StreetType is blank in both Table
A and Table B for these specific streets.

Because it can't seem to match the StreetType field, the record from Table A
appears in the queried data, but the matching data from Table B is blank.
Any idea how I can get it to work?

 

[J_Goddard via AccessMonster.com]

Are the 'blank' fields StreetType really the same in both tables, i.e. might
it be Null in one, and blanks or a zero-length string in the other? Those
won't match.

John

 

[JornyO]

They are both Text fields with nothing in them.

Are the 'blank' fields StreetType really the same in both tables, i.e. might
it be Null in one, and blanks or a zero-length string in the other? Those
won't match.

John

 

[J_Goddard via AccessMonster.com]

The problem is that if the StreetType fields are Null (real Null's, not
blanks or zero-length strings) in Table A and Table B, they will NOT match,
because in MS Access Null <> Null.

You have two ways around this. First is to change the tables so that
StreetType is Required, but can contain zero-length strings. The query
should then work.

The second way is to create a query using Table A, but instead of StreetType,
use an expression NZ([StreetType]," ") to convert the Nulls to blanks.. Do
the same for Table B, Then make another query which links these two queries
(instead of Tables A and B themselves) using the three address fields as you
did before.

John

They are both Text fields with nothing in them.

Are the 'blank' fields StreetType really the same in both tables, i.e. might
it be Null in one, and blanks or a zero-length string in the other? Those

[quoted text clipped - 16 lines]

 

[JornyO]

Thanks for your responses. I guess I must be still missing something after
several years of working with Access though.

How exactly so I tell the difference between a Null Text field and a Text
field that contains a zero-length character string? The StreetType field in
both tables was created when the tables themselves were created, and only
those streets with a StreetType had data added to them. So does that make
the blank entries Null, or zero-length character strings? And if they are
Null, how exactly would one fill the blanks with zero-length character
strings so that the join could find a match?

Thanks.

The problem is that if the StreetType fields are Null (real Null's, not
blanks or zero-length strings) in Table A and Table B, they will NOT match,
because in MS Access Null <> Null.

You have two ways around this. First is to change the tables so that
StreetType is Required, but can contain zero-length strings. The query
should then work.

The second way is to create a query using Table A, but instead of StreetType,
use an expression NZ([StreetType]," ") to convert the Nulls to blanks.. Do
the same for Table B, Then make another query which links these two queries
(instead of Tables A and B themselves) using the three address fields as you
did before.

John

They are both Text fields with nothing in them.

Are the 'blank' fields StreetType really the same in both tables, i.e. might
it be Null in one, and blanks or a zero-length string in the other? Those

[quoted text clipped - 16 lines]

appears in the queried data, but the matching data from Table B is blank.
Any idea how I can get it to work?

 

[J_Goddard via AccessMonster.com]

Hi -

If [field1] contains Null, IsNull([field1]) returns True.

The blank entries in the StreetType field in your tables probably contains
Nulls. To change them to zero-length strings, first change the Allow Zero
Length property of the StreetType fields to Yes (in table design view).

To change the Nulls to zero-length strings, you can use an update query,
putting "" in the Update To for [StreetType], and Null in the criteria for
[StreetType]

John

Thanks for your responses. I guess I must be still missing something after
several years of working with Access though.

How exactly so I tell the difference between a Null Text field and a Text
field that contains a zero-length character string? The StreetType field in
both tables was created when the tables themselves were created, and only
those streets with a StreetType had data added to them. So does that make
the blank entries Null, or zero-length character strings? And if they are
Null, how exactly would one fill the blanks with zero-length character
strings so that the join could find a match?

Thanks.

The problem is that if the StreetType fields are Null (real Null's, not
blanks or zero-length strings) in Table A and Table B, they will NOT match,

[quoted text clipped - 19 lines]