Field Is null 2

[Taff82]

Hi all,

I have a table with about 20 fields in and what I am trying to do is create a query which will show me all records with one or more fields missing.

Can someone point me in the right direction.

Many Thanks

Taff

 

[BNPMike]

Go into the QBE grid, include all relevant fields and on successive criteria rows enter "is null" under one field. This will give you SQL with "where FieldA is null or FieldB is null or ... etc

 

[Taff82]

Thanks for the reply but could you elaberate a bit more please.

Thanks

Taff

 

[lespaul]

add each field you want to check to the grid

in the criteria lines put:


Field1 Field2 Field3 Field4
IsNull
IsNull
IsNull
IsNull

Leslie

 

[BobJacksonNCI]

Taff82,

Note that Leslie did not put the IsNull on the same line for each field because that would have given you the records where all fields are null - an and condition.

The way she showed you it's an OR so that if any of the fields contain null the row will be returned.

Also, the assumption is that showing nothing means the field is Null. Without going into boring detail, Null is not the same as "" (nothing inside the quotes). If you set up the query as she indicated and do not get the expected results, you'll have to change IsNull, possibly to IsNull OR ""...



HTH,
Bob

 

[Taff82]

Brilliant thanks for the help.

Much appreciated.

Taff.