Dcount in report

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Dcount in report

Post by scottb »

Hello,
In a report I have placed a text box within which I am trying to return the count of records from tblActionItems (table) where Status (field) is not “Completed”. Can I do this with Dcount or is there a better approach? I have heard Dcount can be slow.
Thank you for any assistance.
-Scott

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Dcount in report

Post by Rudi »

I'm not sure if there is a faster way or not, but the general syntax for DCount is like this:

=DCount("[Status]", "tblActionItems", "[Status] <> 'Completed'")

Ideally it would be better to refer to the primary key field, or at least a field that will never contain blanks. For example:

=DCount("[ActionID]", "tblActionItems", "[Status] <> 'Completed'")
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dcount in report

Post by HansV »

Or even

Code: Select all

=DCount("*", "tblActionItems", "[Status] <> 'Completed'")
If Status can be left blank, you should use

Code: Select all

=DCount("*", "tblActionItems", "[Status] <> 'Completed' Or [Status] Is Null")
for the condition "[Status] <> 'Completed'" only counts records for which Status does have a value, but that value is not equal to 'Completed'. It doesn't count records for which Status is empty.

Since this is only a single text box, it doesn't matter that you use DCount.
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Dcount in report

Post by scottb »

Thank you both! On a more specific report I would like to be able to do the same thing but only counting where [ProjectID] from the report = [ProjectID] from tblActionItems and Status is <>"Completed" or is null. I am trying to use the expression builder and look at examples but they seem to stop at dcount(expression,domain,criteria). Thank you again for your help. I appreciate it.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dcount in report

Post by HansV »

Does this do what you want?

Code: Select all

=DCount("*","tblActionItems","[ProjectID]=" & [ProjectID] & " And ("[Status] <> 'Completed' Or [Status] Is Null)")
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Dcount in report

Post by scottb »

I am getting "the expression entered contains invalid syntax"

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dcount in report

Post by HansV »

Sorry, I forgot to remove a double quote. It should have been

Code: Select all

=DCount("*","tblActionItems","[ProjectID]=" & [ProjectID] & " And ([Status] <> 'Completed' Or [Status] Is Null)")
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Dcount in report

Post by scottb »

Thank you Hans. This will be very helpful and I understand the syntax better now.

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Dcount in report

Post by scottb »

Hello,
I am trying to modify this slightly to be able to count from the same table (tblActionItems) where the value of the field ActionType = "Risk". The Completed criteria should remain the same.
I am trying the following but getting an #error result:

=DCount("*","tblActionItems","[ProjectID]=" & [ProjectID] & " And ([Status] <> 'Completed' Or [Status] Is Null And [ActionType]=’Risk’)")

Any help with the syntax would be appreciated.
Thank you.
-Scott

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dcount in report

Post by HansV »

You have used curly quotes around Risk instead of straight quotes. And I think a closing parenthesis is placed incorrectly. Does this do what you want?

Code: Select all

=DCount("*","tblActionItems","[ProjectID]=" & [ProjectID] & " And ([Status] <> 'Completed' Or [Status] Is Null) And [ActionType]='Risk'")
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Dcount in report

Post by scottb »

Yes it does. Thank you Hans. Much appreciated.