Counting Values in SubForm Using IIf

  • Thread starter Thread starter sljack63
  • Start date Start date
S

sljack63

This formula gives me an error:
=Count(IIf([frmAgRecsData].Form![Q'd]="Q",0)) What am I doing wrong
here? There are only two possible values contained in this field
[Q'd], those being "Q" or "NQ." I want to count only the values that =
"Q" and put that value on the main form.
 
This formula gives me an error:
=Count(IIf([frmAgRecsData].Form![Q'd]="Q",0)) What am I doing wrong
here? There are only two possible values contained in this field
[Q'd], those being "Q" or "NQ." I want to count only the values that
= "Q" and put that value on the main form.
Click to expand...

You're making a couple of mistakes. First, the aggregate function don't
act on form controls, they act on fields in the form's recordsource (or
else on expressions defined on those fields). Second, the Count
function counts records, so it's not going to pay attention to the
result of the expression you give it.

Try summing instead:

=Sum(IIf([Q'd]="Q", 1, 0))

That assumes that [Q'd] is a field in the form's recordsource.

Alternatively, you could take advantage of the fact that the logical
condition "True" is represented internally as -1, and write this:

=Abs(Sum([Q'd]="Q"))
 
Thank you for such a prompt reply. Unfortunately, you have left me
completely and utterly confused. Why would I want to sum a text field?


Maybe it is my fault for not properly explaining what I am trying to
accomplish: The field is contained in a subform form. I am trying to
add a unbound text box to the main form to total the number of times
the field [Q'd] appears in the subform with a value = "Q."

I tried =Sum(IIf([Q'd]="Q", 1, 0)) and it gave an #Error.
 
Thank you for such a prompt reply. Unfortunately, you have left me
completely and utterly confused. Why would I want to sum a text
field?
Click to expand...

You're not summing the field, you're summing the numeric result of
either a function (IIf) or a conditional expression that returns a
logical value.
Maybe it is my fault for not properly explaining what I am trying to
accomplish: The field is contained in a subform form. I am trying to
add a unbound text box to the main form to total the number of times
the field [Q'd] appears in the subform with a value = "Q."

I tried =Sum(IIf([Q'd]="Q", 1, 0)) and it gave an #Error.
Click to expand...

I didn't understand that the totalling text box is on the main form,
while the field to be summed is on the subform. Here's how you do that:

1. Create a text box in either the Form Header or Form Footer section of
the subform. This text box does not have to be visible, and probably
won't be -- in fact, the whole section need not be visible, if you
aren't using it for anything else. (Both controls and form sections
have a Visible property that you can set to No on the Format tab of the
property sheet.)

2. Name this text box "txtCountQs".

3. Set its ControlSource property to the expression I gave before:

=Sum(IIf([Q'd]="Q", 1, 0))

4. On the main form, have a text box to display the value of the text
box on the subform. The ControlSource of *that* text box will be:

=[frmAgRecsData].[Form]![txtCountQs]

That ought to do it, provided that "frmAgRecsData" is the name of the
subform control on the main form, and not just the name of the form
object it displays.
 
Okay, got it! And it worked. One more question: How do I calculated
my average "Q" rate? I know that if I have 9 total records, and 7 of
those records contain a "Q", then my Q-Rate is 77% How do I put this
into an equation on a control on the subform? I tried Avg([Q'd]),
which of course does not work and interestingly, it screws up the
control on the main form you just helped me fix.
 
Okay, got it! And it worked. One more question: How do I calculated
my average "Q" rate? I know that if I have 9 total records, and 7 of
those records contain a "Q", then my Q-Rate is 77% How do I put this
into an equation on a control on the subform? I tried Avg([Q'd]),
which of course does not work and interestingly, it screws up the
control on the main form you just helped me fix.
Click to expand...

Untested, but try putting a control on the subform (as before) having
this ControlSource:

=Sum(IIf([Q'd]="Q", 1, 0))/Count(*)

Name that text box "txtQRate", and put a control on the main form to
pull its value:

=[frmAgRecsData].[Form]![txtQRate]
 
Before reading your most recent reply, I tried this and it worked:

I put a control on the subform with this formula:
=Avg(IIf([Q'd]="Q",1,0))

Then added a control to the main form = [frmAgRecsData].[txtAvgQRate]
and used the property sheet for format it to show a percentage. Voila!
It worked!

Thanks for all of your help!
 
Before reading your most recent reply, I tried this and it worked:

I put a control on the subform with this formula:
=Avg(IIf([Q'd]="Q",1,0))
Click to expand...

Even better than mine!
 
Back
Top