Record Count

LQ

Registered User.
Local time
Today, 19:09
Joined
Apr 5, 2001
Messages
145
This seems like it should be simple, but...(hmm, I seem to preface most of my posts like this!)I can't figure out a way to generate a record count at the bottom of my report. I have tried putting =count(*) and all sorts of variations in the report footer, but then I get a "# error" at the bottom of the report. Don't know if it matters, but my report is generated by a dynamic SQL statement (users enter criteria on a form).

Thanks for any suggestions!
 
What would youre recordcount be of, I mean What recordsource?

RecordSource.RecordSet.RecordCount

I am pretty Sure Count(*) is for something in a query!

Just guessing here!


[This message has been edited by Surjer (edited 10-23-2001).]
 
Try:
=Count(SomeUniqueField)
 
I tried =count([idnum]) in the report footer, but I am still getting that # error. Idnum is an autonumber field in my table, btw.

Any other suggestions?

Thanks....
 
Open the properties dialog for the control that contains the =Count() function in its recordsource. On the "Other" tab, look at the "Name" property. If it is the same name as any other field or control, that is your error.
 
I checked and it's not the same name as anything else. The name that I am using as the recordsource, btw, should be the name of the unique identifying field on the report, right, not the name of the field in the table itself? So what I actually have as my control source for txtcount (the control in the footer) is =count([txtidnum]), where txtidnum is the name of the autonum field on my report.

What else could I be doing wrong?

Thanks.
 
Just for Kicks and Grins try idnum instead of txtidnum.
 
Maybe you could bypass with with an iif statement in the query then sum the record..

it would look something like this in the query:

countfield: iif(idnum<>0,1,0) THEN in the report do a sum for countfield.

just my way around.
 
=Count(*)
WILL work. Make sure that the "Name" field on the "Other" tab of the properties list for this control is NOT the same as any name that exists in the recordsource for the report.
 
Believe it or not, I still haven't gotten this to work! I have tried a control source of =count(*), =count([idnum]) and =count([txtidnum]). And in order to make sure that the name field for the control is not the same as any other field, I renamed the control txtmickeymouse, which I know for a fact is not the name of anything else on my form.

Does anyone have any other ideas of why this isn't working? I know I must be doing something wrong somewhere, but I can't for the life of me figure it out!

TIA
 
Try:
Delete the recordsource
Save the report
Re-add the recordsource
 
When you say "delete the record source", do you mean that I should go into the report's properties and just click on record source and delete the name of the table? If that is how I am supposed to do it, then I tried that and it still didn't work!

I am very puzzled by this, but thanks for the suggestions...please keep 'em coming if you've got any more
smile.gif
 
No, I'm done. Rebuild the report.

=Count(*)

Is the correct syntax.
 
Well, I did finally get the count of records to work, although it is not where I want it to be. Previously, I had been trying to put the textbox with the record source of =count(*) in the page footer, but I always got a #error. If I place the record count in the detail section of the report, however, it actually does count the number of records. However, this does me no good...I get a record count of the total number of records after each and every record in the report, which seems redundant and kinda stupid.
So where else would I put this text box?

Thanks for any suggestions.

btw, I did rebuild the report, Pat, but with the same results...the =count(*) will only work if I put it in the detail section.

Oh, one other thing....I just noticed that even when I get the record count to print in the detail section, it's printing the total count of all the records in the table, not the number of records in the recordset that's been specified by the SQL statement.

[This message has been edited by LQ (edited 10-30-2001).]

[This message has been edited by LQ (edited 10-30-2001).]
 
Well you sent us on a merry chase there by telling us you wanted the count at the bottom of the report when you were actually trying to put the count in the page footer. The page footer is placed on each page and you CANNOT place controls with domain functions as their controlsource there as you have discovered. If you really want counts per page, you'll need to define your own accumulators and break logic with VBA. Access won't do it automatically for you as it will with section breaks.

As to what count(*) is actually counting, I can assure you that it is using the recordsource of the report whatever that is. The section footer in which the Count(*) is placed is the only thing that will affect its results. In the ReportFooter, it counts the entire recordset. In a Group footer, it counts the detail records for that group. It makes NO sense to put a count() in the detail section. If you do, Access just gives you a count of the entire recordset.
 
Sorry if I led you on a wild goose chase...I guess I didn't communicate my problem very well. I don't completely understand your explanation of accumulators. I am still in the "crawl" stage of learning this stuff, although I really do try to educate myself as much as possible.

So what I take from your explanation is that I can't put the count in the detail *or* the page footer, only the group footer or the report footer. I am going to try and create a report footer and put the count in there, since having a group footer won't work for this report.

Thanks for sticking with the problem! Now at least I understand why what I was doing wasn't working.
----------------------------------
Got it to work...and boy, do I feel dumb that it took so long to figure out!


[This message has been edited by LQ (edited 10-31-2001).]
 

Users who are viewing this thread

Back
Top Bottom