Counting J's in a Report

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I have a text Box in a report (txtJoiningStatus) in which the letters J F or
I are entered
from a form. I want to count in the report footer a textbox with the total
number of J's
that are in the report.
Any ideas, does it need a macro?
Thanks
Jim
 
If the report is based on a query add an unbound text box
with this in the Control source

=DCount("[txtJoiningStatus]","[queryname]","[txtJoiningStatus]='J'")

Jim
 
That'll only tell you how many rows in the table have J in txtJoiningStatus,
not how many Js there are in txtJoiningStatus.

To count how many Js are in a text box, you can use the following function
(assuming you're using Access 2000 or newer):

Function CountInstances( _
ByVal ToSearch As String, _
ByVal ToFind As String) As Long

CountInstances = (Len(ToSearch) - _
Len(Replace$(ToSearch, ToFind, vbNullString))) _
\ Len(ToFind)

End Function

In your case, you'd want something like CountInstances(Me.txtJoiningStatus,
"J")

What this does is replace all of the occurrences of ToFind with a null
string ("") and then determine how much shorter the modified string is.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Jim/Chris said:
If the report is based on a query add an unbound text box
with this in the Control source

=DCount("[txtJoiningStatus]","[queryname]","[txtJoiningStatus]='J'")

Jim
-----Original Message-----
I have a text Box in a report (txtJoiningStatus) in which the letters J F or
I are entered
from a form. I want to count in the report footer a textbox with the total
number of J's
that are in the report.
Any ideas, does it need a macro?
Thanks
Jim


.
 
Hi Jim
Many thanks, works exactly right!
Your time is much appreciated
Jim

Jim/Chris said:
If the report is based on a query add an unbound text box
with this in the Control source

=DCount("[txtJoiningStatus]","[queryname]","[txtJoiningStatus]='J'")

Jim
-----Original Message-----
I have a text Box in a report (txtJoiningStatus) in which the letters J F or
I are entered
from a form. I want to count in the report footer a textbox with the total
number of J's
that are in the report.
Any ideas, does it need a macro?
Thanks
Jim


.
 
Jim,

For the record, another way to do it, possible simpler and certainly
more efficient, would be to put this in the control source of the
textbox in your report footer...
=Sum(Abs([txtJoiningStatus]="J"))

--
Steve Schapel, Microsoft Access MVP

Hi Jim
Many thanks, works exactly right!
Your time is much appreciated
Jim

If the report is based on a query add an unbound text box
with this in the Control source

=DCount("[txtJoiningStatus]","[queryname]","[txtJoiningStatus]='J'")

Jim

-----Original Message-----
I have a text Box in a report (txtJoiningStatus) in which

the letters J F or
I are entered
from a form. I want to count in the report footer a
textbox with the total
number of J's
that are in the report.
Any ideas, does it need a macro?
Thanks
Jim


.
 
Hi Steve
Yes, I pasted that in the report footer, but I get the msg box: Enter
Parameter Value
The report is based on a query of course.
What does Abs mean?
Regards
Jim

Steve Schapel said:
Jim,

For the record, another way to do it, possible simpler and certainly
more efficient, would be to put this in the control source of the
textbox in your report footer...
=Sum(Abs([txtJoiningStatus]="J"))

--
Steve Schapel, Microsoft Access MVP

Hi Jim
Many thanks, works exactly right!
Your time is much appreciated
Jim

If the report is based on a query add an unbound text box
with this in the Control source

=DCount("[txtJoiningStatus]","[queryname]","[txtJoiningStatus]='J'")

Jim


-----Original Message-----
I have a text Box in a report (txtJoiningStatus) in which

the letters J F or

I are entered

from a form. I want to count in the report footer a
textbox with the total

number of J's
that are in the report.
Any ideas, does it need a macro?
Thanks
Jim


.
 
Jim,

The Abs function converts to a positive value. If the value of
txtJoiningStatus is "J", then [txtJoiningStatus]="J" evaluates to True
which has a value of -1, so the Abs converts this to 1 and then the Sum
adds these up and gives a count of those records where you have "J" in
this field.

The parameter value prompt normally would indicate a spelling error in
the expression.
 
Hi Steve
Thanks for the explanation. Although the JoiningStatus text box in the
report was
txtJoiningStatus the field from the query was JoiningStatus.
Removing the txt from the formula has produced the right result.
Many thanks
Jim

Steve Schapel said:
Jim,

The Abs function converts to a positive value. If the value of
txtJoiningStatus is "J", then [txtJoiningStatus]="J" evaluates to True
which has a value of -1, so the Abs converts this to 1 and then the Sum
adds these up and gives a count of those records where you have "J" in
this field.

The parameter value prompt normally would indicate a spelling error in
the expression.

--
Steve Schapel, Microsoft Access MVP

Hi Steve
Yes, I pasted that in the report footer, but I get the msg box: Enter
Parameter Value
The report is based on a query of course.
What does Abs mean?
Regards
Jim
 
Hi Douglas
Thank you for your detailed explanation.
Thankfully in this instance there can only be one J in the text box!!
Jim

Douglas J. Steele said:
That'll only tell you how many rows in the table have J in txtJoiningStatus,
not how many Js there are in txtJoiningStatus.

To count how many Js are in a text box, you can use the following function
(assuming you're using Access 2000 or newer):

Function CountInstances( _
ByVal ToSearch As String, _
ByVal ToFind As String) As Long

CountInstances = (Len(ToSearch) - _
Len(Replace$(ToSearch, ToFind, vbNullString))) _
\ Len(ToFind)

End Function

In your case, you'd want something like CountInstances(Me.txtJoiningStatus,
"J")

What this does is replace all of the occurrences of ToFind with a null
string ("") and then determine how much shorter the modified string is.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Jim/Chris said:
If the report is based on a query add an unbound text box
with this in the Control source

=DCount("[txtJoiningStatus]","[queryname]","[txtJoiningStatus]='J'")

Jim
-----Original Message-----
I have a text Box in a report (txtJoiningStatus) in which the letters J F or
I are entered
from a form. I want to count in the report footer a textbox with the total
number of J's
that are in the report.
Any ideas, does it need a macro?
Thanks
Jim


.
 
Back
Top