Count *

[OldBirdman]

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

[jaxjagfan]

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").

[OldBirdman]

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

[missinglinq]

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)>

[OldBirdman]

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