Calculating sub-totals of a text-content field in a report

[3734kumo]

I need to count the occurance of specific text words in a given field. There are a finite number of words, so I can do a compare and then increment the proper counter. DCount does not work since I am not sure of the syntax. I do get numeric values, but they do not relate to my problem.

Also, if I do this in VB, how do I "attach" the code to the 'group' level so that I can then print the values?

 

[pdldavis]

Hi, depending on how you want to display your text counts, you could do something like this in a control if dcount is what you wish to do:

=DCount("[supln]","tblDailyrpts","([supln] ='" & "Johnston" & "')&quot

-where you are counting the field [supln](superintendents last name)from the table "tbldailyrpts" where the field[supln] = the text value of "Johnston".
The syntax is correct - came out of one of my reports.

Hope that helps.


Below is a complex dcount, which evaluates a certain date and divides the result by the outcome of an IIf statement
as Access documentation is a bit light on utilizing these functions.

=DCount(&quot;[PullDate]&quot;,&quot;tblCWR&quot;,&quot;([SuSys]= '&quot; & [txtSuSys] & &quot;' And [pullDate]<> #01/01/00#)&quot/IIf(DCount(&quot;[PullDate]&quot;,&quot;tblCWR&quot;,&quot;[SuSys] = [txtSuSys] &quot<>0,DCount(&quot;[PullDate]&quot;,&quot;tblCWR&quot;,&quot;[SuSys] = [txtSuSys] &quot,1)

 

[3734kumo]

Thanks pdldavis.

I forgot to mention that I need to have a subtotal in each group footer. When I place the code as given to me, I get within each footer the same number, that number being the TOTAL of all of the item in the entire report. It seems to me that somehow Access is executing the over the entire report every time there is a group break! Strange to me.

What am I doing wrong?

 

[pdldavis]

Hi, the reason it is doing this is because Dcount in the first example is pulling all instances of the requested text from the table when the criteria is met. For a group footer, Dcount will have to be restricted to something that matches the text in main table and a control or controls in the report. In the case below, it is counting the field pulldate, where [Susys] (field in the table), matches [txtSusys] (text box in the report) and any dates that are not equal to #01/01/01#

=DCount(&quot;[PullDate]&quot;,&quot;tblCWR&quot;,&quot;([SuSys]= '&quot; & [txtSuSys] & &quot;' And
[pullDate]<> #01/01/00#)&quot

I have not used Dcounts in the footer section for sub-totals but I think it can be done, although it may not really be what you are looking for. Someone else may be able to elaborate more than I on this.

Here's another way though, that might be more appropriate for your needs.

you could add to your underlying query a field something like this:

Cnt:iif([yourfield]=&quot;YourText&quot;,1,0)

the query will display 1's for every instance the field matches &quot;YourText&quot;.

Stick a text box in the detail section of the report and set its property to sum over the group and turn that box invisible - named txtCnt. I usually turn the text font to red so anyone following me knows they are looking at an invisible box when in design view.

Place one more text box in the group footer that equals txtCnt - =txtCnt - no sum over group.

You could build any number of instances like this directly into your query and accomplish the same result. I know that works in group footers.

Hope that helps.

 

[raskew]

Function StrCount(ByVal TheStr As String, theitem As Variant) As Integer
'------------------------------------------------------------------
' PURPOSE: Counts the numbers of times an item occurs
' in a string.
' ARGUMENTS: TheStr: The string to be searched.
' TheItem: The item to search for.
' RETURNS: The number of occurences as an integer.
'
' NOTES: To test: Type '? StrCount(&quot;The quick brown fox jumped over
' the lazy dog&quot;, &quot;the&quot in the debug window.
' The function will return 2.
'------------------------------------------------------------------
Dim strHold As String, itemhold As Variant
Dim placehold As Integer
Dim i As Integer, j As Integer

strHold = TheStr
itemhold = theitem
j = 0


If InStr(1, strHold, itemhold) > 0 Then
While InStr(1, strHold, itemhold) > 0
placehold = InStr(1, strHold, itemhold)
j = j + 1
strHold = Mid(strHold, placehold + Len(itemhold))
Wend
'Debug.Print &quot;StrCount= &quot; & j
End If
StrCount = j
End Function