Query Not Displaying All Records

[Droogie]

I have a query that will not display records from fields that aren't required
but are related to a parent table. For example:

The "TitleID" field relates to the "Title" table's primary key. This field
is optional and if a user selects nothing than the default value is zero.
However, with referential intregy deselected the form accepts the zero but
when I run a query it will not report the record back to me. When I go into
my table I can see it but when I run the query I can't. I've also manually
gone into the table and changed the foreign ID to 1 and then run the query
and it works.

Help!

 

[Jerry Whittle]

Open the query in design view. Double click on the line between the two
tables until a dialog box shows. Click on the second option. Run the query
and see what happens. If it isn't right, try the third option.

 

[John Spencer]

Foreign keys should almost never have a default value. Normally, I set
all foreign keys to be null.

As noted else thread, you should be able to show your records from the
Title table if you change the type of join.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Droogie]

John,

What do you mean by "null"? The fields that aren't required in my database
have a default value of "0". Would that be considered null? Please explain.

 

[John Spencer]

Null is no value at all. (looks like the field is blank)

Zero is a value.

I was not saying that you should not have zero as a default. You can have a
default value for a field UNLESS the field is part of a relationship. In
that case, it is ALMOST always a bad idea to have a default value (at least
in my opinion).


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..