Counting Total Records and Filtered Records (1 Viewer)

[shwin]

Hi All!

I have a form which has a filter by form feature. The navigational bar at the botton of the form does display how many records I have and also how many records have been filtered, but I would like to display this number on the top of the form. In other words, I would like to record how many records were filtered out and how many records exist total. I think there should be an easy way to do this but I can't get it to work. Any help would be appreciated!

Thanks guys!

 

[aleb]

In form design go to the form footer or header insert two unbound fields
In properties of one go to record source and type:
=count([yourfield])
in second type:
=DCount("[yourfield]", "yourtable",)
First will give you the number of filtered records
Second will give you the number of records in the table.

 

[shwin]

The number of Filtered Records is not working

Thanks for your help, Aleb!

Unfortunately, this still is not working. I am able to get the total number of records showing but the textbox where I wanted just the number of filtered records shows #Error. Any other suggestions would be great!

Thanks!!

 

[KenHigg]

Could you post exactly what you have in the count and dcount functions?

ken

 

[shwin]

For my DCount which is working fine I have:

=DCount("[txtProjNum]","MainTable")

MainTable is the table on which the data is stored and txtProjNum is a primary key.

For my count control source I have this:

=count([txtProjNum])

It is just returning "#Error" in the textbox. A

ny help with this would be greatly appreciated. I was also thinking of maybe using the RecordCount feature but I am not sure how to do this.

If you can think of an optimal method for this problem, please feel free to enlighten me. Thanks again!

 

[KenHigg]

Is this a form / subform scenario? Do you have your count control on the main form?

 

[shwin]

I have the count control on the main form. There is no subform.

 

[KenHigg]

So this is a continuous form with a header and footer?

 

[shwin]

Yes...that is correct. I am new to Access so I am not entirely familiar with what you mean by continuous form. I am trying to catch this information in the header however. I think it is a continuous form. I am sorry I am not at all helpful while you're trying to help me out!

Thanks!

 

[KenHigg]

A continuous form shows more than one record at a time and you set it up in the proprties box for the form.

???
ken

 

[shwin]

Thanks for the explanation. I am actually modifying a database that was made earlier by someone else. It is NOT a continuous form but is a single form.

 

[KenHigg]

Oh, well that requires a differnet approach. You need to do another dcount() function. And just put form filter criteria in the dcount() criteria.

what is you form filter criteria?

ken

 

[shwin]

Hi Ken,

This is where I get a bit confused. I am using the filter by form method and so the user can select criteria for multiple fields and then it filters it. That probably does not answer your question but I am not sure how to answer it.

Essentially, my filter by form works fine and the navigation buttons on the bottom of the form show how many records were filtered and whatnot. I am trying to reproduce those numbers and put them on my header. I appologize for my confusing explanation.

Please advise.

 

[KenHigg]

Hum...

Never done that. Maybe someone else can help???

ken

 

[shwin]

Thanks for your help anyways. Do you think that RecordCount feature might apply here? That is just a thought. I am not sure how to use that but I thought it might work.

 

[aleb]

Is it possible for you to post a dbase itself ?
Well ... I tried to do the following : I have a table. Based on that table I have created a form. I put those two fields on the form and you know ... access didn't care where I put them ... in a footter, in a header, in details ... it did still showed me the correct number of records.
I suspect that you were confused with what access showes and the properties of this form. On the navigation part at the bottom of the form you will actually see the first number !! this number is a serial number of the current record shown of the entire record set. The second number actually shows the number of filtered records. As you see in the example I have attached as a jpg file - I have filtered some records ... top two fields show me how many records in the table ... the second field shows me how many were filtered, if you look at the bottom it shows me the sequential number of the record and the number of records filtered ... Is this what you see on your form ?
It would be great if you will post the dbase or at least the snapshot of the form in design view.

 

[shwin]

Hi Aleb,

Thanks so much for all of your help with this. What you have is exactly what I need. I am not able to post my database to this forum unfortunately but I did figure out what I am doing wrong.

I want to count the ProjNum for each record. The ProjNum field has a field name of txtProjNum and its control source is ProjNum.

=Count(controlsource)
=Count(ProjNum)

This should give the correct number. My issue now is that even though this is right, the db never returns a number as it is calculating forever. It then stops running and I have to click "End Now". My database is huge with over 4000 entries and growing!

I am attaching two screenshots of my database for you. One is a design view and the other is a shot of the error I get. My thought is that the db is too big to count using this method so I need to find another method?

Thanks!

 

[aleb]

Well ... I see the dcount function works properly
Now to the count function
I suspect that you form just doesn't understand to what field you are relating.I also suspect that on the subforms you have attached to the main form there is also another [projnum] field. Is it true ? As you can see I have 14,000 records so it is not a problem.
You can send me an e-mail on aleb@tengizchevroil.com just zip it ... I can accept up to 4 megs.

But before try to place the unbound fields on the subforms. see what will happen.

 

[shwin]

Thanks for all of your help Aleb! My counter is now working but there is still a problem. It takes such a long time to open the form now since I guess it is counting all of the records. It is taking several minutes in fact which is not right. Once it finally loads it works as it should but do you have any idea why it is loading so slowly?

Thanks!