469,927 Members | 2,022 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Count *

675 512MB
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
4 1638
jaxjagfan
254 Expert 100+
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
OldBirdman
675 512MB
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
3,532 Expert 2GB
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
OldBirdman
675 512MB
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.

Similar topics

22 posts views Thread by Ling Lee | last post: by
6 posts views Thread by Geetha | last post: by
5 posts views Thread by Eric Johannsen | last post: by
23 posts views Thread by Gary Wessle | last post: by
22 posts views Thread by MP | last post: by
3 posts views Thread by Auddog | last post: by
7 posts views Thread by Chris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.