Dcount for multiple criteria

[janith]

Hi,

Hope some one can help me with the dcount formula for a text box on form. I want to count the number of items pending or left blank for a particular queue for the user.

User is fosusername()
Table is tblmain
Fields are queues, status.

The formula should count for that user from that table for that particular queue (ccbr) the status is " pending " or "blank". Blank here means there is no record.

Please help....

 

[pbaldy]

This should get you started:

http://www.mvps.org/access/general/gen0018.htm

 

[janith]

That link is for dlook up.

 

[pbaldy]

First line in the link:

Note that the same logic applies to most Domain Aggregate Functions (DMax, DMin etc.)

 

[MarkK]

Criteria for all the domain aggregate function will follow the same rules.

 

[janith]

OK thank you both.. Will try out

 

[plog]

Here's your Dcount reference: http://www.techonthenet.com/access/functions/domain/dcount.php

The criteria portion works the same in most all Dfunctions. You would use " AND " between your critria elements:

"[ThisField]=7 AND [ThatField]="pending"

 

[janith]

How about if the field is null and I want to count that as well.. I can enter " pending " but what about a null field?

 

[pbaldy]

Try

"...AND FieldName Is Null"

 

[janith]

Sure will give it a try.. Thank you all..

 

[janith]

For status here I'm using [status] = "pending" or [status] IsNull
Still getting #error... Please advise

 

[pbaldy]

It would help if you posted your actual formula. Inside the quotes of the criteria, you'd have to use single quotes around pending, and there's a space between Is and Null. There's a different function named IsNull(), but the usage would be different.

 

[janith]

I'm still getting the #error in the field... posting the control source formula ..please advise what's missing.

=DCount("adviserall","tblmain","[queue type] ='MSCCBR' and [status] = 'Pending' or Is Null)

I'm sure there might be comma error somewhere, please guide..

 

[arnelgp]

=DCount("adviserall","tblmain","[queue type] ='MSCCBR' and [status] = 'Pending' or [status] & '' = ''")

 

[janith]

Sure will try with this.. Thank you

 

[plog]

"[queue type] ='MSCCBR' and [status] = 'Pending' or [status] & '' = ''"

When mixing ANDs and ORs you need to use parenthisis.

I believe the above criteria will always evaluate to True when the [status] field is Null, no matter the [queue type] value.

 

[janith]

I'm not sure plog... Are you suggesting to make changes to the last part of the formula?!! Ie; with regards to null value..

 

[plog]

(Criteria1 AND Criteria2) OR Criteria3
If Criteria3 is true no other parts matter--the whole thing will be true.


Criteria1 AND (Criteria2 OR Criteria3)
If Criteria3 is true, Criteria1 still matters, in fact it determines what the whole thing evaluates to.

Remember 6th grade math and order of operations (https://en.wikipedia.org/wiki/Order_of_operations)? That's exactly what this is. Parenthesis then ANDs/ORs

 

[janith]

Well I still get a zero value with the above formula... Please help out

 

[janith]

Well if they is no other way to fix this I guess will have to use two controls to show count for pending and blank records!!!