Using DCount function in a Report group footer

DCount expects 3 String arguments:

* 1st String argument specifies which Field from the Domain you want to count.

* 2nd String argument specifies the Domain / Source that DCount will look at.

* 3rd String argument (which is optional) specifies additional criteria if you only want to select records / rows in the Domain that satisfy the criteria.

From what you posted, I think there are some confusions on the Field name. The expression you posted have both

"Task ID" (1st argument) and "TaskID" (twice in the 3rd argument). Suggest you check the spelling of the Field names in your Table and try again ...

It is possible to include the double-quote in a double-quote-delimited String. However, to delimit Field name in Access / JET, you need to use the square bracket [ ], e.g. [Task ID], not single or double-quotes.

The Domain must be a Table or a saved Query, not a "group" as defined by the "Sorting and Grouping" in the Report.

Perhaps, you can use a hidden TextBox [txtCount] on your Report with ControlSource as

= 1

and set the Property "Running Sum" to Yes/True.

Since you use the keyword DISTINCT, I guess that there are duplicates that you don't want to count, you may need to use sub-group for each [Task ID], place the TextBox [txtCount] in the GroupFooter of the sub-group (set the Visible Property of the TextBox and the GroupFooter of the sub-group to No/False so that they are not visible).

Check Access Help topic "Calculate a running sum" which can explain better than I can ...

LizF@RMIT wrote:

Thanks but I still can't work this out. My statement now looks like this: =DCount([Task ID],[Tasks],"[Task ID] IN (SELECT DISTINCT [Task ID] FROM [Tasks]")

I don't know if this is all of the problem or only part of it - your parentheses don't match (you have two opening and only one closing).

ALSO doesn't DCount need ALL the parameters to be passed as strings You need quotes around the first two parameters.

I suggest it needs to be something like this:

=DCount("[Task ID]","[Tasks]","[Task ID] IN (SELECT DISTINCT [Task ID] FROM [Tasks])")

Note the extra closing parenthesis.

Let us know if it still gives an error after changing that.

Van is right about the square brackets - the best way to specifiy a field or table name is to enclose in in square brackets, not in quotes. Quotes will work most of the time, but are not the best way to do it. The problem you had with nesting the quotes is one reason to use brackets instead.

The square brackets tell the system without any doubt that what's inside is a field or table name. The quotes will allow you to use a field name with a space in it, but they don't necessarily tell the system that it's a table or field. Say you had a field name that was Select. The only failsafe way to force it to recognise it as a field name and not as the start of a SELECT statement is to put it in brackets. Quotes won't be enough in these cases.

When you get in to more convoluted sql queries (your SELECT..FROM is sql, whether you were aware of it or not), using the brackets can also make your code much more readable and easier to troubleshoot.

LizF@RMIT wrote:

Is there any way to get more information from Access on the details of the '#Error'

Not easily, I think. But you can use a query to test the code you're putting in the report (basically your report formula is a mini-query). You will get more informative error messages from there, and be able to preview the result.

1. Create a new query in design view, then switch to sql view (there's a button on the toolbar, or right click in any of the background areas of the design window).
2. This will put you in a blank text entry window (it might have put some text in it, maybe "SELECT;" - if it has, delete that).
3. Paste the text from your report field into the text window.
   • I should add, this text window makes no attempt to be an sql editor, or even a text editor, but if you're only doing simple stuff that won't matter.
4. To make it valid sql, you need to replace the = with SELECT, so it will be:
   SELECT DCount("[Task ID]","[Tasks]","[Task ID] IN (SELECT DISTINCT [Task ID] FROM [Tasks])")
5. Close the sql window and give the query a name, then run the query. It should return a datasheet showing the count of unique Task IDs.
6. Another note - if you go back into edit mode of this query, Access will have probably have added a ; to the end of the statement. That's not really important for what you're doing, but don't include that in your report code.

If you haven't already discovered this trick - when you're building a report, it can be easier sometimes to use the query editor to build the report formulae. On any query you already have that returns a single value, go into sql view to see the code behind the query. This code can be copied and pasted into the report field. Put an = in front and, in general, remove the starting SELECT.