Solved: Count If Text

[oliverralph7]

I am at a loss. I am new to working with access and I am looking to fill in a field on a form.

I have three fields as shown below

Field 1-contains "Yes", "No", or "N/A"
Field 2-contains "Yes", "No", or "N/A"
Field 3-Count If Field 1 and Field 2 has Yes or No


So for Example if the record showed the following:

Field 1=Yes or No
Field 2=Yes or No
Field 3=2

Field 1=N/A
Field 2=N/A
Field 3=0

Field 1=Yes or No
Field 2=N/A
Field 3=1

Field 1=N/A
Field 2=Yes or No
Field 3=1

I am looking to get Field3 to count only the specific record not all the records.

[Mavyak]

SELECT
FIELD1,
FILED2,
IIF([FIELD1] IN('Yes', 'No') AND [FIELD2] IN('Yes','No'), 2, IIF([FIELD1] IN('Yes', 'No') OR [FIELD2] IN('Yes', 'No'), 1, 0)) AS FIELD3FROM
MYTABLE

[oliverralph7]

Where do I input this?

[Mavyak]

That is an SQL statement that will get you your results display records of field1, field2, and field3 as derived from the values of field1 and field2.

[oliverralph7]

So do I put this in the control source for the field 3 or do I put select Build Event and enter this into Expression Builder, Macro Builder, or Code Builder?

I am sorry to be a pest.

[oliverralph7]

I have uploaded the spreadsheet for review. On the Form titled "HCF Audit"

Fields
Correct TOS and Suffix Selected-contains "Yes", "No", or "N/A"
Correct POT (Place of Treatment)-contains "Yes", "No", or "N/A"
Correct DOS entered on Maintenance Screen-contains "Yes", "No", or "N/A"
Total Possible Points-Is where I need help

So for Example if the record showed the following:

Correct TOS and Suffix Selected=Yes or No
Correct POT (Place of Treatment)=Yes or No
Correct DOS entered on Maintenance Screen= Yes or No
Total Possible Points=3

Correct TOS and Suffix Selected=N/A
Correct POT (Place of Treatment)=N/A
Correct DOS entered on Maintenance Screen= N/A
Total Possible Points=0

Correct TOS and Suffix Selected=Yes or No
Correct POT (Place of Treatment)=Yes or No
Correct DOS entered on Maintenance Screen= N/A
Total Possible Points=2

ETC.......

I am looking to get the field Total Possible Points to count only the specific record not all the records. So if I were to go to the next record the Total Possible Points field would reflect 0 until information was entered on the 2nd record.

Is it possible to update the field Total Possible Points in the underlying table for each record?

[CreganTur]

If you want this to update every time you move to a different record, then I suggest using the On Current event. You may need to use RunCommand acCmdSaveRecordto save the new record before moving to the next one... test it and see.

For your coding, I developed this Select Case series:
Private Sub Form_Current()
Dim Score As Integer
Score = 0
Select Case Me.CorrectDOS
Case "Yes"
Score = Score + 1
Case "No"
Score = Score + 1
Case "N/A"
Score = Score + 0
End Select
Select Case Me.CorrectPOT
Case "Yes"
Score = Score + 1
Case "No"
Score = Score + 1
Case "N/A"
Score = Score + 0
End Select
Select Case Me.CorrectTOS
Case "Yes"
Score = Score + 1
Case "No"
Score = Score + 1
Case "N/A"
Score = Score + 0
End Select
Me.TotalPosPoints = Score
End Sub

I've attached your example database with the changes I made.

HTH:thumb

[oliverralph7]

You Rock! Thanks