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
Problem with DCount
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Problem with DCount
Peter N
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem with DCount
Hi Peter,
Welcome to Eileen's Lounge!
Does the following work?
=Abs(Sum([NewNot]="New"))
Welcome to Eileen's Lounge!
Does the following work?
=Abs(Sum([NewNot]="New"))
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Problem with DCount
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.
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
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem with DCount
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.
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
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Problem with DCount
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
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem with DCount
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.
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
Hans
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Problem with DCount
A useful concept to exploit. Now if only I can remember it for the next time I need it in two or three years! Thanks, Hans
Peter N
-
- StarLounger
- Posts: 72
- Joined: 10 Feb 2010, 15:45
- Location: Kitchener, Ontario
Re: Problem with DCount
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
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem with DCount
Glad to have been able to help! It is a useful technique indeed, I use it quite a lot.
Best wishes,
Hans
Hans