DCount with multiple createris (1 Viewer)

janith

CPA
Local time
Today, 15:08
Joined
Apr 11, 2012
Messages
65
Hi,

Hope someone can help me with this problem. I've a DCount formula on a control source its purpose is to count number of records for a particular user logged on that system and the status field showing as "TBW" for them.

here's the formula:

=DCOUNT ("Status", "tblmain", "Citrix Ids = "&' [Forms]![qrymaintable]![npname]' And "[status] = 'TBW'")

for some reason its counting the total records in the table 'tblmain' and not based on the creteria status = 'TBW'

fyi...I'm using the =fosusername() to capture user ids which is stored on the text control 'npname'.

status is my field name , tblmain is the table name , citrix ids is the field name which has a list of ids

Any help to resolve this issue is highly appreciated....

thank you
 

MarkK

bit cruncher
Local time
Today, 02:38
Joined
Mar 17, 2004
Messages
8,178
I don't see how that doesn't cause an error. I would expect . . .
Code:
"CitrixIds = '" & [Forms]![qrymaintable]![npname] & "' And Status = 'TBW'"
. . . or you might need [Citrix Ids] as the field name.

Hope this helps,
 

janith

CPA
Local time
Today, 15:08
Joined
Apr 11, 2012
Messages
65
Thank you will try that.. Hopefully should work..
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
=DCOUNT ("*", "tblmain", "[Citrix Ids] = '" & [Forms]![qrymaintable]![npname] & "' And [status] = 'TBW'")
 

janith

CPA
Local time
Today, 15:08
Joined
Apr 11, 2012
Messages
65
Thank again arnelgp.. Will let know the outcome
 

janith

CPA
Local time
Today, 15:08
Joined
Apr 11, 2012
Messages
65
Tried all the options.. Still can't make it work... It still shows all records.. Any other suggestions??
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
how about using the function straight to your query:

=DCOUNT ("*", "tblmain", "[Citrix Ids] = '" & fosUserName() & "' And [status] = 'TBW'")
 

sneuberg

AWF VIP
Local time
Today, 02:38
Joined
Oct 17, 2014
Messages
3,506
Suggest building a query in the query builder that gives you the answer you want and then use the where part of the sql you end up with in your DCount.
 

janith

CPA
Local time
Today, 15:08
Joined
Apr 11, 2012
Messages
65
Thanks for getting back so quickly... Surely will try out and let know
 

janith

CPA
Local time
Today, 15:08
Joined
Apr 11, 2012
Messages
65
Still not working.. Not sure what might be the problem
 

Minty

AWF VIP
Local time
Today, 09:38
Joined
Jul 26, 2013
Messages
10,353
So what have you tried - post the code...
 

janith

CPA
Local time
Today, 15:08
Joined
Apr 11, 2012
Messages
65
Code suggested by arnelgp.. With fosusername
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
don't tell me that you also have an apostrophe on your field value!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,169
what datatypes are fields:
citrixid and status

nevermind, obviously its text.
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 02:38
Joined
Oct 17, 2014
Messages
3,506
what datatypes are fields:
citrixid and status

I don't think knowing that is going to help. From the very beginning when the OP should have been getting an error she was getting the count for all of the records. I don't think we'll ever determine what's wrong here by focusing on this DCount. I believe at this point we need to see the database or the part of it that has this problem.
 

BlueIshDan

☠
Local time
Today, 06:38
Joined
May 15, 2014
Messages
1,122
janith Could you copy your whole code snippet in
Code:
 [/ CODE] braces for us please? =]
 

Users who are viewing this thread

Top Bottom