Counting occurrences of a value in a datasheet subform

[Beemerang]

Hello Gurus!

I have searched this forum but none of the related posts have given me any insight into my problem.

I have subform (datasheet) with a column for gender indicating "Male" or "Female" I wish to place the totals for each gender (and total overall) on the main form footer. I have tried objects of every type with calculated controls using conditional count formulas such as =Count(IIf([Gender]="Male",0)) but I get #error on everything.

Any tips or pointers on where I could be going wrong?

Beem

 

[CazB]

Me again

try

=-Sum([Gender]="Male")

Note the - before the Sum.... if you don't put that in, you'll get a negative number

 

[Beemerang]

Me again

try

=-Sum([Gender]="Male")

Note the - before the Sum.... if you don't put that in, you'll get a negative number

Hello You Again!

Ok, I added that as the controlsource for an unbound text box in the main form footer with a full reference to the control on the subdatasheet form but it still gives me an #error. Should I place the control on the subform footer instead?

B

 

[CazB]

In a word - yes

If you don't want it visible there, you can always refer to 'it' on the mainform rather than trying to do the calculation on the mainform, iyswim?

 

[Beemerang]

Okay, I've tried putting it on the footer of the datasheet form but despite setting its visible property to yes, it doesn't appear! Where am I going wrong?

B

 

[CazB]

oh booger, of course, if it's a datasheet then it won't be visible...

OK, in that case, add a text box control on your main form and then set the controlsource of that text box to be the text box you can't see on the datasheet, lol...... that should work!

 

[Beemerang]

Okay, I figured out that a datasheet cannot display a footer so I added thos control anyway and created another control on the main form footer that links to this one. It still gives me #error. Any ideas?

B

 

[Beemerang]

oh booger, of course, if it's a datasheet then it won't be visible...

OK, in that case, add a text box control on your main form and then set the controlsource of that text box to be the text box you can't see on the datasheet, lol...... that should work!

LOL snap!!

Seems like I still have a calculation issue on the original control though!

 

[CazB]

Just to find where the error is.... try displaying your subform as a continuous form rather than a datasheet - does the calculation in the footer work then?

 

[Beemerang]

Good idea!. Nope, still gives #error.

 

[CazB]

Hmm, ok, in that case, you need to check your field names... does the field 'gender' appear in your datasheet?

 

[Beemerang]

Yep, checked the field names by using the builder in case I had misspelled something or messed up syntax. Thanks so much for trying to help, I'll keep trying.

Beem

 

[CazB]

Rest assured it'll be something simple I'll check back next week and see how you get on!