When a field being queried is blank?

[Guest]

Everything is working except when I query a field and the desired record's
field is blank. I get nothing. If I put something in that field and then
query that field for what I put in (I'm querying a couple of fields) then it
works. I assume it has something to do with what a blank field is
stored/represented as.

Thanks.

 

[Allen Browne]

Explicity include the nulls in your criteria.

For example if you have criteria of:
Not "New York"
use:
Is Null OR Not "New York"

More info:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html

 

[Guest]

Thanks Allen, but I didn't include all the data because I didn't think it was
neccessary. Being refreshed, I think my probelm is the table I'm querying(?)
only contains identifiers (Auto Numbers) from other tables or empty fields,
blanks. Because these identifiers are meaningless to others they query with
words listed in another table that are related by Auto Number to the table
I'm interested in. There is no Auto Number for a "blank". So when they
query a "blank" there is no corresponding Auto Number in my table of interest
and therefore no matching records.

 

[Allen Browne]

Look at the suggested link.

It discusses how to handle outer joins between the tables, as well as the
critieria.

 

[Guest]

Thanks Allen. I now understand the differences. I guess what I have is a 0
string. That field is not an unknown, I know that it is not associated with
that record. If I create a possible item called "NONE" there is an
AutoNumber and everything works as long as they enter "NONE" for the search
criteria. However, it seems silly for them to type "NONE", I'm sure there is
a more elegant way around this, I just don't have the experience to know what
it is.