By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Count *

100+
P: 675
I have read a million posts about how to find the number of records in a query. The statement "SELECT COUNT(*) FROM table" is very nice, but how do I "know" the result.

I need a statement such as: i = "SELECT COUNT(*) FROM table", so i is the number of items in table. I can write the appropriate WHERE clause.

Currently I am using a hidden listbox, where listbox.rowsource="SELECT ..." and then i = listbox.listcount. Is there a more efficient way?

OldBirdman
Dec 28 '07 #1
Share this Question
Share on Google+
4 Replies


jaxjagfan
Expert 100+
P: 254
I have read a million posts about how to find the number of records in a query. The statement "SELECT COUNT(*) FROM table" is very nice, but how do I "know" the result.

I need a statement such as: i = "SELECT COUNT(*) FROM table", so i is the number of items in table. I can write the appropriate WHERE clause.

Currently I am using a hidden listbox, where listbox.rowsource="SELECT ..." and then i = listbox.listcount. Is there a more efficient way?

OldBirdman
Try using Dsum function. Exclude the criteria if you want a count of all records.

i = DSum([yourfield],"yourtablename").
Dec 28 '07 #2

100+
P: 675
DSUM and similar functions require a "table name" or "query name". I need to knoiw how many rows for either a query built "on-the-fly", or for an underlying dataset for a form or control.

If strSQL = "SELECT * FROM tableA WHERE [Value]>100000;", then I want to assign to i the number of records that are selected.

If I have a listbox lbxA, then if lbxA.RowSource=strSQL, I can then assign i by i=lbxA.ListCount. Simple, but klutzy as I have to have a hidden control lbxA on my form. But it does work. I should be able to do this by the COUNT function, without the hidden control. How?

OldBirdman
Dec 30 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
Not only does DSUM require a "table name" or "query name," but it, amazingly, does exactly what its name suggests, totals the values in a given field of said table/query! Unless the value in each instance of this field was 1 it wouldn’t be much use in determining the number of records. Perhaps jaxjagfan was thinking of Dcount().

Perhaps you could incorporate your code with the example given by ADezii in his Testing for an Empty Recordset. Towards the end he addresses the issue of counting records.

Linq ;0)>
Dec 30 '07 #4

100+
P: 675
I noticed that DSUM wasn't quite what I wanted. I am slow to respond, because an answer, although not exactly what I want may contain the answer that I do want. In this case, I first considered doing a SELECT where there was an additional field, lets just say "1 as ZZZ". Now I can do a DSUM, as each record has a field with a value of "1". Of course, I also found DCOUNT.

Further investigation, starting from DSUM found ii = rst.RecordCount, which was a nice, generic solution to many counting issues.

My thanks to jaxjagfan for pointing me in this direction, and thank you missinglinq for monitoring the post, and being sure that I understood.

OldBirdman
Jan 10 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.