Counting J's in a Report

[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

 

[Jim/Chris]

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

 

[Douglas J. Steele]

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)

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]

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


.

 

[Steve Schapel]

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]

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

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


.

 

[Steve Schapel]

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.

 

[Jim]

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

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

 

[Jim]

Hi Douglas
Thank you for your detailed explanation.
Thankfully in this instance there can only be one J in the text box!!
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)

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


.