MS Access- Count the values in a table row that are equal to 1 but not the other values of 0 and -1 then add them

We have a simple database with a table that has 53 Columns in it, each column can contain a 1,-1, or 0. We need to count the number of 1's and -1 in each record to get the total of each. I would like it to calculate on the form as the each column completed, the default value in the column is Zero. Once we can get it working on the form we want it on a report that will be one page with all the fields listed with either Yes=1 No=-1 NA=0 for the user being audited (this is completed). The calculation for the report will be the Total "1" and Total "-1". Basically I think what I need is a countIF statement but can't seem to make it work.

Also note that the fields in the table for the calculation are Number fields, that get their data from a lookup.

Below is the code I tried with no luck and I am sure its something simple missing (at least hope it is). Also been testing with only a few fields to keep it manageable till its working well. Open to other suggestions as well.

The result I would be expecting from this is 8 since NameID=-1 and MilVet and Vet_status = 0

=Count(iif([NameID]=1,1,0, [DOB]=1,1,0,[Phone_Cell]=1,1,0,[Email]=1,1,0,[LanguageEntered]=1,1,0,[InterpreterEntered]=1,1,0,[RaceEntered]=1,1,0,[EthnicEntered]=1,1,0,[ReligionEntered]=1,1,0,[VET_Status]=1,1,0,[MILVET]=1,1,0,)

Aggregate functions aggregate records, not fields. Also, the IIf syntax is wrong. Use normal arithmetic and just add the result of IIf applied to each field. Consider example with 3 fields - assumes every field has a value and not Null because if any field has Null the result of arithmetic will be Null:

=IIf([NameID]=1,1,0) + IIf([DOB]=1,1,0) + IIf([Phone_Cell]=1,1,0)

=IIf([NameID]=-1,1,0) + IIf([DOB]=-1,1,0) + IIf([Phone_Cell]=-1,1,0)

=IIf([NameID]=0,1,0) + IIf([DOB]=0,1,0) + IIf([Phone_Cell]=0,1,0)

I expect any expression involving 53 fields will be too long for query as there is a limit of 1024 characters in a cell of query design grid. I am not sure if there is a limit for expression in textbox ControlSource. Aside from that, it's a lot of tedious typing. Your alternatives are a VBA procedure or redesign data schema so that record aggregation can be used. An entity-attribute-value structure might be appropriate. A UNION query can often rearrange fields to a vertical E-A-V structure but UNION is limited to 50 SELECT rows and your data would need 53.

So, a VBA function in a general module that can be called from query, textbox, or other VBA would be like:

Function CountData(lngID As Long, intValue As Integer) AS Integer
Dim rs AS DAO.Recordset, x As Integer
Set rs = CurrentDb.OpenRecordst("SELECT * FROM table WHERE AuditDetailsKey = " & lngID)"
If Not rs.EOF Then
For x = 1 to 53
    CountData = CountData + IIf(rs(x) = intValue, 1, 0)
    'or this version: If rs(x) = intValue Then CountData = CountData + 1
Next
End If
End Function

That procedure makes assumption that the recordset first field is record identifier and next 53 fields are -1/1/0 data fields. Also, if you want this count to update with input to each textbox of form, that will require committing the record to table after each input. Code (macro or VBA) in each control AfterUpdate event to save record. An easy way to do this is to build a VBA function behind the form to save record then select all 53 data controls at once and enter function call into AfterUpdate property: =MySaveFunctionName().