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
Dcount in report
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Dcount in report
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'")
=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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78242
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dcount in report
Or even
If Status can be left blank, you should use
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.
Code: Select all
=DCount("*", "tblActionItems", "[Status] <> 'Completed'")
Code: Select all
=DCount("*", "tblActionItems", "[Status] <> 'Completed' Or [Status] Is Null")
Since this is only a single text box, it doesn't matter that you use DCount.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Dcount in report
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.
-
- Administrator
- Posts: 78242
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dcount in report
Does this do what you want?
Code: Select all
=DCount("*","tblActionItems","[ProjectID]=" & [ProjectID] & " And ("[Status] <> 'Completed' Or [Status] Is Null)")
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Dcount in report
I am getting "the expression entered contains invalid syntax"
-
- Administrator
- Posts: 78242
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dcount in report
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
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Dcount in report
Thank you Hans. This will be very helpful and I understand the syntax better now.
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Dcount in report
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
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
-
- Administrator
- Posts: 78242
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dcount in report
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
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Dcount in report
Yes it does. Thank you Hans. Much appreciated.