Counting a specific value in a subform. 1

[maxxev]

I have a subform based on a Crosstab query, of which I would like to count the instances of a specific result ("Y") in each column of the subform.


E.g.

Form: "frmAllergensInProducts"
SubForm: "S-frmAllergensinProducts"

I'd like to count the subform columns.
Wheat,Barley,Oats, etc.
Where they have the value of "Y".

Can you please help me work out the code for this, i've tried various "count" codes however I have been unable to make them work and i'm wondering if that is because the form is based on a crosstab?

Cheers

 

[kjv1611]

Can you please help me work out the code for this, i've tried various "count" codes however I have been unable to make them work and i'm wondering if that is because the form is based on a crosstab?

Tell us what you tried for starters.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dhookom]

The subform displays records from a table/query. Can you use DCount() on the main form to pull the value directly without concern about the subform?

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

Please note o'm really not very good at the programming side of Access...

I tried putting the following in the footer of the MAIN form

=Count([S-frmAllergensinProducts]![Wheat])
=Count([Wheat])
=Count([form]![frmAllergensInProducts]![S-frmAllergensinProducts]![Wheat])
=Count(Me![S-frmAllergensinProducts].Form!Wheat)

At this point I was just trying to get the cells to count (I have no idea how to go about only couting the "Y" results....)

Cheers

 

[dhookom]

If you don't want to follow my earlier suggestion, you can try add a footer to your subform and include a text box:
Name: txtCountWheat
Control Source: =Sum(Abs(Wheat="Y"))
Then on your main form, add a text box with a control source of:
=[S-frmAllergensinProducts].Form.txtCountWheat

This assumes the name of the subform control is [S-frmAllergensinProducts] and the data type of Wheat is text.

Duane
Hook'D on Access
MS Access MVP

 

[maxxev]

That's excellent and works great.

Thank you very much.