Count number of times value appears in columns

Question

Due to some strange circumstances, random long text fields in my Microsoft Access database appear to be corrupting and being replaced with "################". I want to be able to count the number of corrupted fields with an SQL query so that I can quickly check if the number has changed.

I have written a query that can count the number of records with corrupted values, but not the total fields (e.g if 5 records have 13 corrupted values, I can get the number 5 but I want the total number of 13). How can I adjust my query?

SELECT Count(*) AS [Number of Errors] 
FROM GPInformation 
WHERE Profile="################" 
   OR Notes="################" 
   OR CriminalConvictionsNotes="################" 
   OR HealthIssueNotes="################" 
   OR NextOfKinAddress="################"

Output:

Answer 1

You can add conditional logic to the select:

SELECT (sum(iif(Profile = "################", 1, 0)) +
        sum(iif(Notes = "################", 1, 0)) +
        sum(iif(CriminalConvictionsNotes = "################", 1, 0)) +
        sum(iif(HealthIssueNotes = "################", 1, 0)) +
        sum(iif(NextOfKinAddress = "################", 1, 0))
       ) AS NumBadValues        
FROM GPInformation 
WHERE Profile = "################" OR
      Notes = "################" OR
      CriminalConvictionsNotes = "################" OR
      HealthIssueNotes = "################" OR
      NextOfKinAddress = "################";