Count how many fields are missing for each record ?

[Nyanko]

Hi,

I have a fairly simple query to weed out all the records in our database that are missing vital pieces of infomation :

SELECT Contacts.Name, Contacts.Address1, Contacts.Address2, Contacts.Town, Contacts.County, Contacts.Country, Contacts.PostCode, Contacts.Telephone, Contacts.Code
FROM Contacts
WHERE (((Contacts.Address1) Is Null) OR ((Contacts.Address2) Is Null) OR ((Contacts.Town) Is Null) OR ((Contacts.County) Is Null));

Is there anyway to count how many fields are missing for each record ?

 

[Mihail]

Create a new field in your query:
NullFieldsCount: IIF(IsNull([Contacts].[Address1]);1;0) + IIF(IsNull([Contacts].[Address2]);1;0) + IIF(IsNull([Contacts].[Town]);1;0) + IIF(IsNull([Contacts].[County]);1;0)

 

[rzw0wr]

try,

  SELECT [B][U]count ([/U][/B]Contacts.Name, Contacts.Address1, Contacts.Address2, Contacts.Town, Contacts.County, Contacts.Country, Contacts.PostCode, Contacts.Telephone, Contacts.Code[B][U])[/U][/B]FROM ContactsWHERE (((Contacts.Address1) Is Null) OR ((Contacts.Address2) Is Null) OR ((Contacts.Town) Is Null) OR ((Contacts.County) Is Null));

Also a good site for SQL,
http://www.w3schools.com/sql/default.asp

Dale