DCount with multiple createris

[janith]

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]

I don't see how that doesn't cause an error. I would expect . . .

"CitrixIds = '" & [Forms]![qrymaintable]![npname] & "' And Status = 'TBW'"

. . . or you might need [Citrix Ids] as the field name.

Hope this helps,

 

[janith]

Thank you will try that.. Hopefully should work..

 

[arnelgp]

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

 

[janith]

Thank again arnelgp.. Will let know the outcome

 

[janith]

Tried all the options.. Still can't make it work... It still shows all records.. Any other suggestions??

 

[arnelgp]

how about using the function straight to your query:

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

 

[sneuberg]

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]

Thanks for getting back so quickly... Surely will try out and let know

 

[janith]

Still not working.. Not sure what might be the problem

 

[Minty]

So what have you tried - post the code...

 

[janith]

Code suggested by arnelgp.. With fosusername

 

[sneuberg]

I suggest uploading your database or a stripped down version of it. Check out http://www.access-programmers.co.uk/forums/showthread.php?t=276413 for how to strip down a database.

 

[arnelgp]

don't tell me that you also have an apostrophe on your field value!

 

[janith]

No I don't

 

[arnelgp]

what datatypes are fields:
citrixid and status

nevermind, obviously its text.

 

[sneuberg]

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]

I was far too entertained with createris!

 

[BlueIshDan]

janith Could you copy your whole code snippet in

 [/ CODE] braces for us please? =]