New to access-- Need help with form

[Csalge]

I have a form that has a combo box that tracks yes, no and NA answers in different fields. For the current form i would like to have an unbound control that counts all the yes, no and NAs or just the Yes and No in multiple fields. I was looking for something like Countif in excel. Please advise.

[June7]

Try:
=Sum(IIf([fieldname]=True,1,0))

[Csalge]

I tried it but it doesn't work. My unbound control does not show anything.

[June7]

It works for me. Guess I will have to examine your db if you want to provide it. Follow instructions at bottom of my post.

Probably a good idea to provide db. I am having difficulty understanding issue in your other thread as well.

[Csalge]

Here you go. The form in Quality Form I have the yes or no fields that I'm trying to sum or count at the bottom of the form but I can't seem to get it to work. Please help. I know that this is not a very smart way to create that form but I didn't know what other way to do it.

Thanks,

[June7]

Use:

=Sum(IIf([question1]=True Or [question2]=True,1,0))

Could have just asked you to show your expression because it is different from what I suggested. Oh well, gave me a chance to see your design and offer other comments, for better or worse. Your data structure is not normalized. Might cause issues.

[Csalge]

I will try that. What do you mean by not being normalized?

[Csalge]

I used
=Sum(IIf([question1]=True Or [question2]=True,1,0))

it did not give me an error but it sums the value in the field even from previous records. Is there a way for to do a sum for just the values on the current form or to get a count of Yes and No?

[June7]

Multiple similar named fields is an indication data is not normalized. If you had to add a new question/answer to the evaluation this would mean modifying table, queries, forms, reports to include a new field in the design. If this won't ever (or rarely) happen or the redesign effort is tolerable then go with what you have.

Aggregate functions (Sum, Count, Avg, etc) calculate on recordsets. Meaning the Sum calculation takes place over a group of records. The expression effectively counts the number of records of the form's dataset that meet the criteria in the IIf condition.

So what do you mean by 'sum for just the values on the current form'? The values of the 'current form' are fields of a single record. Maybe you don't want to sum records, you want to sum/count fields? This is an example of complications due to non-normalized data.

=IIf([question1]=True,1,0) + IIf([question2]=True,1,0) + IIf([question3]=True,1,0) ...

[Csalge]

Yes, that's what I'm trying to do but i still not able to get it to work even after trying :
=IIf([question1]=True,1,0) + IIf([question2]=True,1,0) + IIf([question3]=True,1,0)

It only counts/sum the first value. I only get a 1.

[Csalge]

I found this and I think that may help me get the calculation I need. However, my problem is that I don't have number values. How would I alter it so that it would only sum if fields are yes:

Public Function basSum(ParamArray varMyVals()) As Variant

Dim Idx As Integer
Dim MySum As Variant

For Idx = 0 To UBound(varMyVals())
If (IsMissing(varMyVals(Idx))) Then
GoTo NextVal
End If
If (IsNumeric(varMyVals(Idx))) Then
MySum = MySum + varMyVals(Idx)
End If
NextVal:
Next Idx

basSum = MySum

End Function

Then in a query an expression would be added:
TOT: basSum([cal1],[cal2],[cal3],[cal4],[cal5],[cal6],[cal7],[cal8],[cal9],[cal10],[cal11],[cal12],[cal13],[cal14],[cal15)

[June7]

Open the quality form independently, not through the main form. The calculation result shows 2. However, those 3 answers displayed are Yes, No, Null. Question3 combobox is not bound. The calculation works. Something about behavior of the main form interferes.

The terms in the calculation for the other questions will have to be different because those are no Y/N field.

The expression will probably be too long for textbox ControlSource so a VBA solution will be needed. So you are correct to try a function. The function you show is using an array. Do you understand using arrays? Try:

For Idx = 0 To UBound(varMyVals())
MySum = MySum + IIf(varMyVals(Idx)=-1 Or varMyVals(Idx) = "Yes",1,0)
Next