null data not showing up if "is null" query criteria

[tcek]

have a a database with one column with sparse empty cells. when querying for
those empty cells i use the "isnull" criteria. the results show nothing
which makes me think there is something in that cell. how do i look for an
empty cell?

 

[Duane Hookom]

The criteria would actually be:
Is Null

 

[tcek]

the real problem is that the "null" criteria is not selecting (or
deselecting) cells that appear to be empty in the database

 

[tcek]

it is a date field and not a number. does this matter?

 

[John Spencer]

Try

WHERE SomeField Is Null Or SomeField = ""

To cover almost all possibilities - Null, a zero-length string, or multiple
spaces you can use

WHERE Trim(SomeField & "") = ""

In Design View (query grid) that would be
Field: Trim(SomeField & "")
Criteria: ""
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[tcek]

"" worked..thanks

Try

WHERE SomeField Is Null Or SomeField = ""

To cover almost all possibilities - Null, a zero-length string, or multiple
spaces you can use

WHERE Trim(SomeField & "") = ""

In Design View (query grid) that would be
Field: Trim(SomeField & "")
Criteria: ""
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Duane Hookom]

You stated "it is a date field". I don't think a date field in Access could
ever store "".