Problem with DCount

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Problem with DCount

Post by petern »

I have a report which is counting visits by clients with the visits sorted by each worker. Clients are either New or they are not (Well, duh, but this is where my problem comes in...) I have a calculated field [NewNot] in the underlying query which writes the word NEW if the client meets the appropriate criteria and puts in an empty string if he/she does not.

What I need to do is count the number of new clients for each worker in the specified period. I'm trying to use DCount to do this and am getting #Error in the field, so clearly my syntax is wrong. Here is what I have in the textbox for New Clients which I have placed in the UserID(Worker) footer :
=DCount("*","qryClientVisitCountWorker","NewNot = 'New'")

I've tried various permutations of this and know that I also need to filter on UserID so I am guessing I need something like:
=DCount("*","qryClientVisitCountWorker","NewNot = 'New' and UserID= '" & [UserID] & "'")
which also gives off #Error
Peter N

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Problem with DCount

Post by HansV »

Hi Peter,

Welcome to Eileen's Lounge!

Does the following work?

=Abs(Sum([NewNot]="New"))
Best wishes,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Problem with DCount

Post by petern »

Hi Hans:

Nice to be here. Feel's just like home with all the familiar faces.

This worked. I actually found a workaround in the meantime since I needed a whole bunch of other stats and I was able to pull the number out of a crosstab.

I would be interested to know how this is working for future reference. Why do I want a Sum instead of a Count or DCount in a situation like this? I looked up Abs which I assume you throw in there to make the number a positive integer.
Peter N

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Problem with DCount

Post by HansV »

The expression [NewNot]="New" returns True = -1 for records that have "New" and False = 0 for records that don't have "New".
So in Sum([NewNot]="New") each record with "New" contributes -1, i.e. the sum is -1 times the number of records with "New".
The Abs function is indeed used to convert the result to a positive number.
Best wishes,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Problem with DCount

Post by petern »

Would it be true to say that IIf statements then actually return true or false (-1 or 0) even though what we are seeing is text (in this case "New")? Are there other built in functions that do something similar?
Peter N

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Problem with DCount

Post by HansV »

If you have a calculated field of the form

NewNot: IIf(<some condition>, "New", "")

NewNot is a text field. It is the comparison operator = that returns a True=-1 or False=0 value, just like < and > and <= and >= and <>.

For example, 17 > 2 is equivalent to -1 since the comparison is true
32 <= 13 is equivalent to 0 since the comparison is false.
"Peter" = "" is equivalent to 0 since the comparison is false.
Best wishes,
Hans

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Problem with DCount

Post by petern »

A useful concept to exploit. Now if only I can remember it for the next time I need it in two or three years! :clapping: Thanks, Hans
Peter N

petern
StarLounger
Posts: 72
Joined: 10 Feb 2010, 15:45
Location: Kitchener, Ontario

Re: Problem with DCount

Post by petern »

Just a followup to let you know that I have already found a use for this new found knowledge of exploiting trues and falses. I needed to pull a subtotal from 3 of six cells in a single row of a crosstab and was able to use DSum and Abs on the underlying table to give me the number I needed. One of the most useful concepts I've learned in the past year or two. Thanks a lot.
Peter N

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Problem with DCount

Post by HansV »

Glad to have been able to help! It is a useful technique indeed, I use it quite a lot.
Best wishes,
Hans