Selecting only blank fields in access 2007

[jlanier]

Let’s say I had a table like below, and I only want to select the name and any field which was blank / null

name,
field1,
field2,
field3,
field4,
field5,
field6,
field7

For example, if the name was ‘John’ and field1, 2, and 6 were blank I would want my results to be

Name Field1 Field2 Field6
John null null null


Any ideas?

 

[plog]

What if you had another row where the Name was 'Mike' and Field1, Field4, Field5, Field6 and Field7 were blank? What if you had a 'Bob' row and he had no blank fields? And 'Tim' who had every Field blank? Including the John example you gave, what would results of that query look like for those 4 records?

 

[wmphoto]

What is the context of this? A query can only filter records, it can't filter fields, but if you want something that will only show blank fields perhaps there is something you can do with a form that can achieve this.

 

[jlanier]

What if you had another row where the Name was 'Mike' and Field1, Field4, Field5, Field6 and Field7 were blank? What if you had a 'Bob' row and he had no blank fields? And 'Tim' who had every Field blank? Including the John example you gave, what would results of that query look like for those 4 records?

Yeah - I see where you're coming from. The results would be a mix, and in some cases every field could be returned (Like the Tim example).

What I'm trying to do is quickly identifiy gaps where data was not entered for a record.

 

[plog]

The simplest method would be to open up the table and sort each field Ascending. The blanks in that column would pop to the top.