DCount - Multiple Criteria (Query Expression)

[Dmak]

I have a calculated field in a query that provides the names for task lists and the number of tasks in each list within parentheses;

NameCount: [TaskListName] & " (" & DCount("*","tblTasks","[TaskListID]=" & [TaskListID]) & ")"

This works nicely and provides;

ListName1 (2)
ListName2 (10)
etc. etc.

The problem is it's counting completed tasks so I am trying to add a second criteria to the DCount in order for it to count uncompleted tasks only.

I have tried several variations of the expression below but don't get the required results.
Independently the criteria work fine but together, not so.

Full Expression;
NameCount: [TaskListName] & " (" & DCount("*","tblTasks","[CompletionDate] Is Null" And "[TaskListID]=" & [TaskListID]) & ")"

DCount only
DCount("*","tblTasks","[CompletionDate] Is Null" And "[TaskListID]=" & [TaskListID])

Any help much appreciated.

 

[pr2-eugin]

See if this works..

NameCount: [TaskListName] & " (" &  DCount("*","tblTasks","IsNull([CompletionDate]) And [TaskListID]="  & [TaskListID]) & ")"

 

[Dmak]

See if this works..

NameCount: [TaskListName] & " (" &  DCount("*","tblTasks","IsNull([CompletionDate]) And [TaskListID]="  & [TaskListID]) & ")"

Perfect, thank you!

 

[Dmak]

See if this works..

NameCount: [TaskListName] & " (" &  DCount("*","tblTasks","IsNull([CompletionDate]) And [TaskListID]="  & [TaskListID]) & ")"

EDIT: Works fine as it is. Cheers.

I feel a touch cheeky asking but how could I include non null blank fields in this?

 

[pr2-eugin]

Well the following will catch both Null and Zero Length Strings..

NameCount: [TaskListName] & " (" &  DCount("*","tblTasks","Len([CompletionDate] & "") = 0 And [TaskListID]="  & [TaskListID]) & ")"