Query referencing a field without a value

[SurveyorinVA via AccessMonster.com]

Good morning,

I have created a query based upon several tables. My main table has fields
that are blank until the field is filled later on, such as the following:
OrderID < Added when record is created
ClientID < Added when record is created
OrderNo < Added when record is created
OrderDate < Added when record is created
FilledBy < Added when the order is filled (different then when created)
OrderComplete < Added when the order is complete (different then when created
& filled)

If I add all the above fields to a query, along with fields from tblClient,
and have a criteria based upon the OrderNo, the query returns a record if ALL
the fields listed have been filled. But if FilledBy and OrderComplete are
still blank, the query returns a blank record.

Is there a work around to this that I am missing where even if these fields
are blank, the query will still return a record when the criteria is met?
Thanks,
CF

 

[Allen Browne]

The solution will be to use outer joins, or to explicitly accepts nulls in
your criteria.

Both issues are explained here:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[John Spencer]

You probably need to change the joins.

Double click on the join line between your main table and the filled by
table.
Select the option that says ALL records in the main table and only those ...
in the Filledby table.

Repeat for the other relationships that are causing you problems.

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

 

[tobesurveyor via AccessMonster.com]

Thanks for the info. Now what happens if the fields that will not be entered
until a later point are in the main table? There would be no join associated
with it. Also in the link you sent to your site, it mentions to put
something like Is Null Or Not "WA", now what happens if it is a date that I
am not sure what it will be?

Thanks,
CF

The solution will be to use outer joins, or to explicitly accepts nulls in
your criteria.

Both issues are explained here:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Good morning,

[quoted text clipped - 22 lines]

Thanks,
CF

 

[Allen Browne]

I'm not sure I understand that.

If you have not entered the records yet, then the query will not be able to
retrieve them.

If there are records in one table, but not in the other, the outer join
handles that.

You do not need to enter Critera.
The criteria limit which records are returned.
If you don't know (and don't care) what dates are returned or needed, don't
use any criteria under the date field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the info. Now what happens if the fields that will not be
entered
until a later point are in the main table? There would be no join
associated
with it. Also in the link you sent to your site, it mentions to put
something like Is Null Or Not "WA", now what happens if it is a date that
I
am not sure what it will be?

Thanks,
CF

The solution will be to use outer joins, or to explicitly accepts nulls in
your criteria.

Both issues are explained here:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Good morning,

[quoted text clipped - 22 lines]

Thanks,
CF

 

[tobesurveyor via AccessMonster.com]

Thanks Allen for the info, I problem was that I was referencing a value in my
main table that was linked to another table, but in the other table that
value did not exist, so it returned an empy record.

I'm not sure I understand that.

If you have not entered the records yet, then the query will not be able to
retrieve them.

If there are records in one table, but not in the other, the outer join
handles that.

You do not need to enter Critera.
The criteria limit which records are returned.
If you don't know (and don't care) what dates are returned or needed, don't
use any criteria under the date field.

Thanks for the info. Now what happens if the fields that will not be
entered

[quoted text clipped - 21 lines]