counting records on a subform that have a specific value in a subform field

[tolgainci]

Hello,

I have a subform that has several fields such as "name", "date" etc. On the footer of the subform I have placed a text field named COUNT that has =Count([ID]) as the control source. On the main form I've placed another text field which has =Subform1.Form!COUNT as the source. This method works perfectly well and displays the total number of records on the subform.
However I need to calculate the number of records that have "test" in the "name" field on the subform, and display it on the main form. I have tried Dcount in the control source e.g :=DCount("[ID]", "Subform1", "[Name] = 'test'"). This approach does actually count the number of "test"s, but unlike the count function it totally ignores the relationships. As a results it displays the number of "test"s from all of the records in the table that the subform is linked to.
I hope you get the picture. I'm very new to Access design as you might have noticed and have little programming background. I would greatly appreciate any help.

Thanx in advance!

 

[bradcccs]

"Name" is a reserved word used by access to refer to the form name - I would recommend avoiding reserved names at all times.



It sounds like your subform is based on the whole table, but is filtering records down by Master / Child fields.

The dcount expression you are using is counting the occurence of "test" in all records. You could add an "And" to you criteria (where?) statement in the dcount. eg:

DCount("[ID]", "Subform1", "[Name] = 'test' And [LinkFieldID] =" & LinkFieldName)

HTH

Brad.

 

[tolgainci]

Thanks Brad,

I will check out your suggestion. In the meantime I've come across another simple solution in the forums, and it has worked for me. On the subform I've replaced the control source of the count text box that was:
=Count([ID]) with:
=Sum(IIf([NAME]="test",1,0))

Though it seems a bit awkward, hasn't caused any problems.

Thanks again,

Tolga