DCount function in subform (1 Viewer)

[rhonda6373]

Hello,

I am trying to count the number of records in a subform where the text box (txtFinal) = RPR-RPR & RTN. The subform is called PartNumbers. The table name the subform is bound to is named Completed.

I tried using =DCount("[Part Number]","Completed","[Final] = 'RPR-RPR & RTN'"). That is counting the total number of records in the table, not the total number of records in the subform. How do I get it to count the total number of records in the subform?

 

[CJ_London]

from the main form you can try

subformcontrolnameforpartners.form.count

 

[rhonda6373]

I am not sure I understand. I only need a count where text box (txtFinal) = RPR-RPR & RTN. Can you give more detail? I am a novice.

 

[CJ_London]

sorry, my suggestion would not apply a filter.

you would need to build a recordset based on what is in your subform

 dim rst as dao.recordset
 dim sqlStr as string
  
 sqlStr="SELECT Count(Product) FROM Completed WHERE [Final] = 'RPR-RPR & RTN' AND " & [COLOR=red]subformcontrolnameforpartners.form.filter[/COLOR] & " AND [COLOR=darkgreen][" & subformcontrolnameforpartners.LinkChildFields & "]=[" & subformcontrolnameforpartners.LinkMasterFields & "]"[/COLOR])
 set rst=currentdb.openrecordset(sqlStr)
 mycount=rst.fields(0)
 set rst=nothing

the bit in red applies any filters the user may have used on the subform and the bit in green applies the relationship between the mainform and the subform - you may have to modify these if control names do not match fields names

 

[vbaInet]

The recordset option would obviously be the quickest option but you just need to remember to call the function in the Apply Filter event of the form.

Another option which should work is using putting =Count(*) in the footer of the subform and referencing that control in your main form.

=Count(*) + IIF([ID], 0, 0)

 

[rhonda6373]

Thanks to both of you. I opted for the second option since I am not good at code.

I tried =IIf([Final]="RPR-RPR & RTN",Count(*),0)

but that is giving me the count of all records if there is only one record where Final = "RPR-RPR & RTN).

However, I don't need the total count, I only need the count of records where Final = "RPR-RPR & RTN". For example if there are 4 records on the subform and only 2 are "RPR-RPR & RTN" I need the count to be 2.

How can I modify the IIf statement to do that? Or is there another way?

 

[vbaInet]

One of:

=Sum([Final]="RPR-RPR & RTN") * -1

=Count(IIf([Final]="RPR-RPR & RTN", 1, Null))

First one is preferential because it performs better.

 

[rhonda6373]

That worked. Just curious, why do you have to put * -1?

 

[vbaInet]

That worked. Just curious, why do you have to put * -1?

The expression will result in a negative total so we need to counter that with -1 or the Abs() function.

 

[NauticalGent]

I beat my brains out trying to figure something like this out and came here to post the question. But before I did, I decided to check to see if some one had the same issue. Glad I did, the "=Sum([Final]="RPR-RPR & RTN") * -1" solution worked life a charm. This site is worth every penny I invested.

Those of you who come here looking for answers should seriously consider donating...

 

[vbaInet]

I'm pleased that you searched before posting.