Counting Records

[Kelly Enright]

When counting records in a report based on a query I need
some way to have Access recognize the text ALL as being
equal to 10 and counting 10 records.

Example:

Date RowNumber SectorNumber
12/12/03 02 01
12/12/03 05 ALL

I need the count to show 11 records rather than 2
I am currently using the =Count(*) function

Is there a way?. Thanks....kbe

 

[Doua]

In your query, add another column that will do your
calculation/conversion (Example: iif([SectorNumber]
="All",10,[SectorName])) for you and then just sum on
this column in your report.

 

[Kelly Enright]

Ok that works as long as there are some ALL records each
month_ =Count(*)+Sum(Expr1). How do I get it to pick up
the Count(*)part of the expression if there are no Expr1
or ALL entries. Thanks much...kbe

-----Original Message-----
In your query, add another column that will do your
calculation/conversion (Example: iif([SectorNumber]
="All",10,[SectorName])) for you and then just sum on
this column in your report.

-----Original Message-----
When counting records in a report based on a query I need
some way to have Access recognize the text ALL as being
equal to 10 and counting 10 records.

Example:

Date RowNumber SectorNumber
12/12/03 02 01
12/12/03 05 ALL

I need the count to show 11 records rather than 2
I am currently using the =Count(*) function

Is there a way?. Thanks....kbe
.

.

 

[Evi]

The thread seems to have got lost for this and your example isn't clear. It
looks more like a Sum rather than a Count since you appear to be adding up 1
and 10 and getting 11. A Count of those records would be 2.

Do you want to add up your SectionNumber field although it is clearly a text
field?
Then you do need that extra field. It will say

SumField: NZ(IIF([SectionNumber]="All", 10, Val([SectionNumber]))


This is the field you will add up in your report
=Sum([SumField])
(though you can make the field invisible and only display the SectionNumber
field)
Evi

Ok that works as long as there are some ALL records each
month_ =Count(*)+Sum(Expr1). How do I get it to pick up
the Count(*)part of the expression if there are no Expr1
or ALL entries. Thanks much...kbe

-----Original Message-----
In your query, add another column that will do your
calculation/conversion (Example: iif([SectorNumber]
="All",10,[SectorName])) for you and then just sum on
this column in your report.

-----Original Message-----
When counting records in a report based on a query I need
some way to have Access recognize the text ALL as being
equal to 10 and counting 10 records.

Example:

Date RowNumber SectorNumber
12/12/03 02 01
12/12/03 05 ALL

I need the count to show 11 records rather than 2
I am currently using the =Count(*) function

Is there a way?. Thanks....kbe
.

.

 

[Kelly Enright]

Hi again: In my "Date Footer" I have the following

Bag Changes for the Month =Count(*)+Sum([Expr1])

In my "Report Footer" I have the same entry. This works
fine as it picks up all of the bag changes YTD. My problem
is that I need a way for "Bag Changes for the Month" to
show the record count when there are no entries in the
[Expr1] field.

Example1:

Date RowNumber SectorNumber

12/12/03 02 01
12/12/03 05 ALL

Total Bag Changes for the Month 11 (This works well)

Example2:

Date RowNumber SectorNumber

12/11/03 02 01

Total Bag Changes for the Month 1 (This will not show on
the report but is included in the YTD totals in
the "Report Footer" which would show 12).

You have all been a great help and I thank you very much.
Compliments of the season to all...kbe