Finding Number Of Rows In A Query (1 Viewer)

[WayneRyan]

Hello All,

I have a Query that has 5218 rows. I just wanted to display the
row count on a form.

DCount("[SomeField]", "TheQuery") returns --> 5095.
DCount("*", "TheQuery") returns --> 5095.

There are NO Nulls in [SomeField] !

That doesn't seem right at all.

Additionally, the query contains exactly two rows for each unique
occurrence of [SomeField].

Select [SomeField], Count(*)
From   TheQuery
Group By [SomeField]
Order by Count(*) ASC

This shows that the "missing" 125 rows in the query have only
one occurrence of [SomeField]. [SomeField] is a text field (8 characters)
with no Nulls.

However, this:

Select * 
Into   NewTable
From   TheQuery

Produces 5218 rows, in perfect pairs. The DCount will work fine when
using the table.

The workaround was to use a Recordset to get the count, but
I'm a little confused as to why the DCount doesn't work
properly AND as to why the Count(*) returns "bad" results.

Any ideas?
Wayne

 

[ajetrumpet]

maybe queries are unpredictable? as in, they need to be run before they can be counted? can you put an SQL statement in the domain argument of dcount? i've never tried that before. i wonder if that would work instead of the name of the actual query?

 

[gemma-the-husky]

are you sure you havent got a "distinct" creeping in their somewhere - to mask the duplicates, as it were