Count Expression

[Guest]

I have several fields in a querry. These fiels have either 'Yes' or 'No'
values. I need an expression that counts all the 'yes' in the previous
fields.

 

[John Vinson]

I have several fields in a querry. These fiels have either 'Yes' or 'No'
values. I need an expression that counts all the 'yes' in the previous
fields.

It depends a bit - are these Text fields with the literal text string
"Yes"? If so:

CountYes: IIF([Field1] = "Yes", 1, 0) + IIF([Field2] = "Yes", 1, 0)...


If it's an Access Yes/No field you can take advantage of the fact that
Yes is stored as -1, No as 0:

CountYes: Abs([FIeld1] + [Field2] + ...)

John W. Vinson[MVP]

 

[Guest]

Thanks John , the first expression worked because it was not an access yes/no
field, but a text string.

I have another question. I am creating a form with a check box.

The check box is for field 1 in the querry (remember, field 1 has either
'Yes' or 'No' values).
If the user clicks the check box and hits the OK button, I want all the
records that have a 'Yes' value in field 1 to be shown. If the user leaves
the check box unclick, I want all the 'No' values in field 1 to be shwon.

How do I link the querry with the check box?

Thanks

I have several fields in a querry. These fiels have either 'Yes' or 'No'
values. I need an expression that counts all the 'yes' in the previous
fields.

It depends a bit - are these Text fields with the literal text string
"Yes"? If so:

CountYes: IIF([Field1] = "Yes", 1, 0) + IIF([Field2] = "Yes", 1, 0)...


If it's an Access Yes/No field you can take advantage of the fact that
Yes is stored as -1, No as 0:

CountYes: Abs([FIeld1] + [Field2] + ...)

John W. Vinson[MVP]

 

[John Vinson]

The check box is for field 1 in the querry (remember, field 1 has either
'Yes' or 'No' values).
If the user clicks the check box and hits the OK button, I want all the
records that have a 'Yes' value in field 1 to be shown. If the user leaves
the check box unclick, I want all the 'No' values in field 1 to be shwon.

How do I link the querry with the check box?

You can use the AfterUpdate event of the checkbox to set the form's
Filter property:

Private Sub chkShowYes_AfterUpdate()
If Me.chkShowYes Then
Me.Filter = "[fieldname] = 'Yes'"
Else
Me.Filter = "[fieldname] = 'No'"
End If
Me.FilterOn = True
End Sub

John W. Vinson[MVP]