Sherry N. said:I am trying to calculate how many times the letter C appears in a text box on
a report and display the total in another text box.
Marshall Barton said:Well, I made a hash of that air code.
I actually tested this one:
Public Function CountChar(strChar As String, _
strText As String) As Integer
Dim pos As Integer
pos = InStr(strText, strChar)
Do Until pos = 0
CountChar = CountChar + 1
pos = InStr(pos + 1, strText, strChar)
Loop
End Function
--
Marsh
MVP [MS Access]
You'll have to write a UDF in a standard module to calculate
the number of Cs. Try something like this air code:
Public Function CountChar(strChar As String, +
strText AsString) As Integer
Dim pos As Integer
pos = Instr(strChar, strText)
Do Until pos = 0
CountChar = CountChar + 1
pos = Instr(pos, strChar, strText)
Loop
End Function
Then the second text box can use the expression:
=CountChar("C", firsttextbox)
Sherry N. said:Thanks for your help. However, it doesn't quite work. I created the module
as you suggested and the number returned was double the amount of records so
I modified it a little and at least got to the point where it counts the
records.
How can I modify this code to only count when the values of my field ("All
CPT Codes Addressed")is equal to "C". Can I use an "If" statement?
Something like If strChar="C" then CountChar=1 and then assign the other
textbox's properties as running sum.
This is the code I have now that just counts all the records and doesn't
discern the "C".
Public Function CountChar(strChar As String, strText As String) As Integer
Dim pos As Integer
pos = InStr(strText, strChar)
Do Until pos = 0
CountChar = 1
pos = InStr(pos + 1, strText, strChar)
Loop
End Function
Marshall Barton said:Sherry N. said:Thanks for your help. However, it doesn't quite work. I created the module
as you suggested and the number returned was double the amount of records so
I modified it a little and at least got to the point where it counts the
records.
How can I modify this code to only count when the values of my field ("All
CPT Codes Addressed")is equal to "C". Can I use an "If" statement?
Something like If strChar="C" then CountChar=1 and then assign the other
textbox's properties as running sum.
This is the code I have now that just counts all the records and doesn't
discern the "C".
Public Function CountChar(strChar As String, strText As String) As Integer
Dim pos As Integer
pos = InStr(strText, strChar)
Do Until pos = 0
CountChar = 1
pos = InStr(pos + 1, strText, strChar)
Loop
End Function
Either I'm completely confused, or this is a different
question than the one you asked earlier.
If you just want to count records where the field [All CPT
Codes Addressed] equals "C", then you don't need a running
sum text box or that function. Just set the footer's text
box expression to:
=Sum(IIF([All CPT Codes Addressed] = "C", 1, 0))
If you really need to have a running sum in the detail
section, then set its expression to:
=IIF([All CPT Codes Addressed] = "C", 1, 0)