Hi Folks,

I have answered my own question so I thought I would share as I couldn't find the solution in any posts. I confess that I don't understand why my results were wrong, but I managed to get them right. :rolleyes:

When using Dcount in a query, I was getting results which did not match the query results. For instance:

Phase_2: DCount("Project_Phase_ID","tbl_Prj_Details","Project_Phase_ID = 2")
gave an answer of 27 when there were in fact 41 projects in that phase.

Searching the forum I came accross this:

Count() always counts the entire domain.

So, Dcount is not counting the record set of my query but something else. I have 4 tables in the query and no idea what domain my dcount was looking at. I presume the various join types were messing with it somehow. :confused:

To get round this, I stripped out the Dcount expressions and changed the query to a make table. I then used the created table as the basis of a query in which I had my Dcounts. The dcount results now agree with the query recordset. :) :) :)

Any background on the bits I clearly don't understand will be gratefully received. I hope this helps someone else sometime.

Kind regards,

Keith.

Was your query name "tbl_Prj_Details"?
Your DCOUNT was looking at column "Project_Phase_ID" in that tbl/qry.

DCount( "A", "B", "C" ) executes

SELECT Count("A") FROM "B" WHERE "C" ;

As FoFa says, unless your nomenclature is awsomely loose, you are not counting a query.

:eek: :eek: :eek:

Put brain in gear before opening mouth! :o

I have just spotted the mistake. I was counting all the projects that are in phase 2 which is indeed 27.

The query was showing the personnel assignments to the projects along with the project phases. I wanted to count the number of personnel assigned to projects in Phase 2 which is 41.

This also explains why my solution of making a table which was then queried and dcounted works fine.

Is there any way to dcount the results of the query within a query? The count I want is on more than one field so I guess I would have to use an expression to create a single countable field.

Thanks for the feedback. It may be painful for the old hands and gurus, but "L" plate Access people such as myself learn heaps sorting out this sort of thing.

Regards,

Keith.