"is not null" doesn't work

[Lifeseeker]

Hi,

I have a very simple query, just one field.....trying to test it out, but I'm not sure why it isn't working....

One column, and the criteria is "is not null".

When run, the query still returns null values.

The field is a look up field, but I don't know if matters or not....

Any thought?

 

[jdraw]

Please describe the lookup aspect?
Also please post the query sql.

 

[Lifeseeker]

Please describe the lookup aspect?
Also please post the query sql.

Hi,

Please see the attached.

In Queries, the criteria for "hospital_type" is "Is not null";however, when the query is run, it still shows everything. The hospital_type is a lookup to another table as you can see in the relationship.

this is quite puzzling as I have never ran into this problem before....so simple...

 

[boblarson]

The criteria would not be Is Not Null but it would be

<> 0

because the field is NUMERIC, and it has a default value set in the table as 0.

AGAIN (and this is about the THIRD or fourth time I've told you this) -

DO NOT USE LOOKUPS AT TABLE LEVEL!!!!

They obscure things and make it harder than it needs to be.

 

[jdraw]

The hospital_type is numeric and defaults to 0, not null

Try where hospital_type <> 0

 

[Lifeseeker]

The criteria would not be Is Not Null but it would be

<> 0

because the field is NUMERIC, and it has a default value set in the table as 0.

AGAIN (and this is about the THIRD or fourth time I've told you this) -

DO NOT USE LOOKUPS AT TABLE LEVEL!!!!

They obscure things and make it harder than it needs to be.

Hi Bob,

By table level look up, did you mean looking up to the ID column(autonumber) of the referencing table?

If I have to get rid of the ID column on the referencing table, I would still need to look up to the next available column on the referencing table, correct?

Take my test database as an example, if I have to use a look up, I would still need to look up to the hospital_type column instead of the ID, wouldn't I?

I understand that if tables have to be set up this way, then I would be able to use "Is Not Null" because then the datatype would be text not number.

But is this what you meant by "do not use table level look up? though?

 

[boblarson]

Take a look at the changes I made to your database tables and query.

If you notice, if you have set up the tables correctly, you wouldn't even need the criteria on the query because if you look, the link between the table and lookup table IN THE QUERY ensures that there is data in the hospital table and that there are no zero or nulls from the hospital table for that type returned.