is null

[slimjen1]

I have a table with a name field and five other fields. I want to create a query where it would tell me if the fields have anything in them or not. I created a query and put in the criteria "is null" and I got nothing in return. Then I put "is null" in the first criteria field and "is null" in the remaining field, but on the "or" criteria line. It worked, but I'm missing records because they dont add up between the number of records in the table and between the nulls and not nulls. Can anyone tell me why and what I am doing wrong please?

 

[vodafrog]

Try an IIF statement in your query, you can then it to give you a value, for example:-


IIF(critria,true,false) syntax

example

IIF(ISNULL(Field1),"Empty","OK")

this would show the word Empty if Field1 is null, and OK if it contains any other value. You can change these

This IIF statement needs to go into the Field section of your query. You will need to type in the expression or use the builder. See exmple: -

Expr1: IIf(IsNull([Last Name]),"Empty","OK")

You can change Expr1 to be something meaningful, but not a field name. Attached a screen shot to help you out.

 

[slimjen1]

Thanks. I'll try this.

 

[neileg]

What do you want to count? Do you want the number of records that have a null in any of the five fields or do you want to know the number of null entries?

How are you counting the records that have no nulls? You should have Is Not Null as criteria in all five fields on the AND line.