Counting 3

[JRWPD]

I wish to generate a report that first counts and shows the count of a number of entries. Then I need to count how many of those entries contain the letter P and show that amount. I am new at this so please be kind.
bill

 

[LonnieJohnson]

If this report has no special grouping, then you can create a report header by going to the design view of the report and from the View dropdown menu option, you will select
Report Header/Footer.

When the Header appears on the report, you can put a text box and in the control source you will put =Count(*). This will tell you how many total records are in the report.

Then put another textbox in the header that has a DCount expression like so...

=DCount("*",[RecordSource],"MyFieldName LIKE '*N*'&quot

You actually have to put [RecordSource] and Access will know you mean the underlying data source. You will put the name of your field in place of MyFieldName.

Hope this helps.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[PaulBricker]

I'm shaking my head, but it tests well on my data. I've never seen a DCount using [RecordSource]. Have a star.

Paul

 

[dhookom]

This solution will test well (but possibly slower) in cases only when you don't open the report with a where condition. For instance, if your OpenReport line of code uses:
DoCmd.OpenReport "rptMyRpt",,,"[Department]='HR'"
The DCount() will fail since it is still looking at all departments. The much more efficient solution that works even if you use a where condition is simply:
=Sum( Abs([MyField] Like "*N*&quot )
DSum() creates another recordset in addition to the report's recordset. Using Sum() uses the recordset already provided by the report so it runs faster.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LonnieJohnson]

Like PaulBricker, I was shaking my head dhookom. I was wondering how you are going to get a "count" from a "sum"??? It works on my data. What happens if the field in question is actually a numeric field? If he wanted all fields that have 99? I guess I could try it.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[PaulBricker]

Duane's inside expression, [MyField] Like "*N*" returns a boolean, -1 or 0, which gets changed to 1 or 0 by the Abs() function which gets summed by the Sum() function. Summing 0's and 1' is the same thing as counting which is why the expression works the way it does.
I had never seen the part of your DCount expression that uses [RecordSource]. I've been using Access since day 1 and it amazes me what there is to learn if you just keep your eyes open.

Paul

 

[JRWPD]

You folks are great to help out like this. The solution worked great. Thanks for all the input.

 

[LonnieJohnson]

Thanks Paul for that breakdown. That is really cool. I have been doing this for 8 years and I still get amazed at little things myself. Thanks Paul, thanks Duane.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[jh3016]

I have to do something similar to the above. However, I need some If's statement, I believe. I need to:

In Fieldname [TypeofMove], I need to count
how many records = "Transfer"
If [TransferDate] >12/31/03 and <2/1/04

Basically, what I'm trying to do is get the amount of transfers by month. I need it to display on the report like Jan Feb Mar Apr and so on. So under Jan would be the number of transfers that happened in the month of January.
Thanks in advance.

 

[dhookom]

I think a crosstab would work great however you haven't provided the table structure, sample data, or the way you would like the results laid out in a report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.