WHERE field = '' <- Not returning data

[pbaugh]

Hi folks,
Im a SQL Server developer who is currently using Access, so I'm
probably doing something really dumb. I have a simple query that is
searching on the criteria WHERE field = '' ie find me the records
where the text field is blank. I have two such records but the query
is returning an empty set. I though at first that maybe it was null so
I tried WHERE field = Null, but no luck there either.

Any ideas?

Many thanks
Pete

 

[John Spencer]

Try

Where Field is Null

Or (perhaps less efficient)

WHERE Field & "" = ""



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

 

[Ken Snell \(MVP\)]

Try this:

WHERE Len(Field & "") = 0

The above will capture both a Null and an empty string value in the field.

 

[pbaugh]

Thanks guys! I was actually investigating doing a Len check so I was
on the right lines!

Thanks again
Pete