Query on blank field

[Rainson]

I am trying to create a querry which will return all records for which a certain field is blank. I have tried (IsNull), (IsBlank) and ("") and none of them are returning any records. The field I am placing the condition on is defined as text. I have verified that the field is not filled with a space, or two spaces, etc.

Any help would be appreciated

 

[mdmilner]

Try len(fieldname) = 0 as your parameter.

In design view, go to a blank column and type into the fieldname area...

len([fieldname])

Lower, in the parameter area type this

= 0

In SQL view it will look like after Access 'creates' it:

WHERE (((Len([fieldname]))=0))

Mike

 

[Rainson]

Thanks mdmilner, I tried that as well and no luck.

I did come up with a way that works, however it takes two steps. In query step one I create a new field using the formula iif(isnull(field1),1,2). Then in query step 2 I use that query as the input and use the criteria "1" to pull only the records I want

What I don't understand is why I can't just use "IsNull" in the criteria against the original database? I thought that using IsNull in the criteria row was evaluated the same as using it as part of a function.

Any ideas?

 

[giacomo]

ok, maybe this is a dumb question but are you actually typing isnull as the criteria or IS NULL?

When I type isnull (no quotes) as my criteria access wraps quotes around it turning it into "isnull", when I type IS NULL (or just null) it doesn't wrap quotes around it and when I run the query I get all the nulls.