Count a field in Report with criteria

V

vvwetwet

Hello,



I have a question about how to count the number or records in a report



my report runs a query on some tables and display the records on it.

in the report, it has a field [LastName], how can I count the number of
records that the [LastName] = "Powell" for example and display the
number of count in the report header?



I tried to make a function in VB for example



Public Static Function Total_Number_Last_name(ArrLastname() As Object)

Dim S As Integer

Dim R As Integer

Dim I As Integer

R = 0



For I = LBound(ArrLastname ) To UBound(ArrLastname )

If ArrLastname (I) <> "Powell" Then

Else

R = R + 1

End If

Next I

Total_Number_Last_Name= ArrRequestType

End Function



and call this function in one of the textbox in my report using

=Total_Number_Last_name([LastName])



However, I found that when I am calling this function from the report
heder, it does'nt seem recognize [LastName] as an array and give me
an "#error"

but if I change my function's argument type to (ArrLastname As Object)

it is then fine



Anyways... any easy way I can work arround so that I can just count the
number of records that has their last name = "Powell" down in my report?



Any suggestion is appreciated!



I am sorry that it may be an easy question for many of you people, but i
am kind of stuck here



Alec
 
D

Duane Hookom

You shouldn't need any code. Try a control source on a text box:
=Abs(Sum([LastName] = "Powell"))
 
T

Terry

Or even:
=Abs(Count([LastName] = "Powell"))

if you want to count them.

Terry
-----Original Message-----
You shouldn't need any code. Try a control source on a text box:
=Abs(Sum([LastName] = "Powell"))

--
Duane Hookom
MS Access MVP


vvwetwet said:
Hello,



I have a question about how to count the number or records in a report



my report runs a query on some tables and display the records on it.

in the report, it has a field [LastName], how can I count the number of
records that the [LastName] = "Powell" for example and display the
number of count in the report header?



I tried to make a function in VB for example



Public Static Function Total_Number_Last_name (ArrLastname() As Object)

Dim S As Integer

Dim R As Integer

Dim I As Integer

R = 0



For I = LBound(ArrLastname ) To UBound(ArrLastname )

If ArrLastname (I) <> "Powell" Then

Else

R = R + 1

End If

Next I

Total_Number_Last_Name= ArrRequestType

End Function



and call this function in one of the textbox in my report using

=Total_Number_Last_name([LastName])



However, I found that when I am calling this function from the report
heder, it does'nt seem recognize [LastName] as an array and give me
an "#error"

but if I change my function's argument type to (ArrLastname As Object)

it is then fine



Anyways... any easy way I can work arround so that I can just count the
number of records that has their last name = "Powell" down in my report?



Any suggestion is appreciated!



I am sorry that it may be an easy question for many of you people, but i
am kind of stuck here



Alec
Click to expand...


.
Click to expand...
 
D

Duane Hookom

I don't think so. Count() will count all non-null values whether they are 0
or -1 or "A" or " ". The expression provided will "count" all the records
that have any value in the LastName field.

--
Duane Hookom
MS Access MVP


Terry said:
Or even:
=Abs(Count([LastName] = "Powell"))

if you want to count them.

Terry
-----Original Message-----
You shouldn't need any code. Try a control source on a text box:
=Abs(Sum([LastName] = "Powell"))

--
Duane Hookom
MS Access MVP


vvwetwet said:
Hello,



I have a question about how to count the number or records in a report



my report runs a query on some tables and display the records on it.

in the report, it has a field [LastName], how can I count the number of
records that the [LastName] = "Powell" for example and display the
number of count in the report header?



I tried to make a function in VB for example



Public Static Function Total_Number_Last_name (ArrLastname() As Object)

Dim S As Integer

Dim R As Integer

Dim I As Integer

R = 0



For I = LBound(ArrLastname ) To UBound(ArrLastname )

If ArrLastname (I) <> "Powell" Then

Else

R = R + 1

End If

Next I

Total_Number_Last_Name= ArrRequestType

End Function



and call this function in one of the textbox in my report using

=Total_Number_Last_name([LastName])



However, I found that when I am calling this function from the report
heder, it does'nt seem recognize [LastName] as an array and give me
an "#error"

but if I change my function's argument type to (ArrLastname As Object)

it is then fine



Anyways... any easy way I can work arround so that I can just count the
number of records that has their last name = "Powell" down in my report?



Any suggestion is appreciated!



I am sorry that it may be an easy question for many of you people, but i
am kind of stuck here



Alec
Click to expand...


.
Click to expand...
Click to expand...
 
T

Terry

OOOooops I stand corrected .

Terry

-----Original Message-----
I don't think so. Count() will count all non-null values whether they are 0
or -1 or "A" or " ". The expression provided will "count" all the records
that have any value in the LastName field.

--
Duane Hookom
MS Access MVP


Terry said:
Or even:
=Abs(Count([LastName] = "Powell"))

if you want to count them.

Terry
-----Original Message-----
You shouldn't need any code. Try a control source on a text box:
=Abs(Sum([LastName] = "Powell"))

--
Duane Hookom
MS Access MVP



Hello,



I have a question about how to count the number or records in a report



my report runs a query on some tables and display the records on it.

in the report, it has a field [LastName], how can I count the number of
records that the [LastName] = "Powell" for example
Click to expand...
and
display the
number of count in the report header?



I tried to make a function in VB for example



Public Static Function Total_Number_Last_name (ArrLastname() As Object)

Dim S As Integer

Dim R As Integer

Dim I As Integer

R = 0



For I = LBound(ArrLastname ) To UBound(ArrLastname )

If ArrLastname (I) <> "Powell" Then

Else

R = R + 1

End If

Next I

Total_Number_Last_Name= ArrRequestType

End Function



and call this function in one of the textbox in my report using

=Total_Number_Last_name([LastName])



However, I found that when I am calling this function from the report
heder, it does'nt seem recognize [LastName] as an
Click to expand...
array
and give me
an "#error"

but if I change my function's argument type to (ArrLastname As Object)

it is then fine



Anyways... any easy way I can work arround so that I can just count the
number of records that has their last name = "Powell" down in my report?



Any suggestion is appreciated!



I am sorry that it may be an easy question for many
Click to expand...
of
you people, but i
am kind of stuck here



Alec


--
Posted via http://dbforums.com


.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A03 - count in report footer gives me #Error? 0
Count function showing #Error when no Record 0
counting records 0
How to count records using count(*) in a chart report 3
Adding Blank Lines to a Report 0
Access Dcount (multiple criteria) 3
Calling a public function from within a report? 2
Count function in a report 1

Top