Prev: Opening a Form
Next: Allow Zero Length property
From: Chris on 11 Mar 2010 09:40
Might be an easy question, but how do I return all records which have
empty cells for the specified FIELD

Example:
Name School Home Child
1. xxxx xxxx xxxx
2. xxxx xxxx xxxx
3. xxxx xxxx xxxx xxxx
4. xxxx xxxx xxxx
5. xxxx

I want to return all records except #3. Because it has all of the
values. I only want to see records missing a value.

I tried to use. IS NULL in each Field in Design View (under OR not
CRITERIA), but it keeps returning everything. Thanks in advance
From: Rich P on 11 Mar 2010 11:10
Hi,

You could do something like this:

Select t1.* from tblx t1
Where not exists (Select t2.* from tblx t2 where (t2.fld1 is not null or
t2.fld1 <> '') and (t2.fld2 is not null or t2.fld2<>'') and (t2.fld3 is
not null or t2.fld3<>'') and (...) and t2.IdentityCol = t1.Identitycol)

This query would require that your table contain an Identity column (a
unique key column) -- usually and Autonum column. In the "not exists"
subquery you are selecting all the rows where none of the fields are
empty or null and excluding these rows from the primary query.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
 | 
Pages: 1
Prev: Opening a Form
Next: Allow Zero Length property