LizF@RMIT


Hi,

I'm having trouble getting a DCount function to work on an MSAccess 2003 report.

I'm using a text box in a group footer - I want it to show a count of the number of distinct records in the group.

Here's what I'm using - which produces the result of '#Error' with no further help.:

=DCount("Task ID","Tasks","TaskID IN (SELECT DISTINCT TaskID FROM R_TaskID)")

I'm aware of the access definition of expr, domain and criteria syntax for this function, however, I'm not sure what to use for the 'domain' in this scenario.

Expr - I gather this should be the field I want to count - 'Task ID' - the field is part of the report detail.

Domain - here's where I'm sure I've gone wrong. I think this should be the group recordset but I don't know how to reference them The report uses a query containing tables and queries - but it doesn't have a name. How do I reference the report group level

Criteria - hopefully I have this right - I am calling a query.

Thanks in advance for any assistance.

Liz




Re: Using DCount function in a Report group footer

Van T. Dinh


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 ...







Re: Using DCount function in a Report group footer

LizF@RMIT

Hi,

thanks but that's not the case. The report field name is "Task ID". The third argument references a query that simply renames "Task ID" to "TaskID" because the syntax wouldn't accept further quotes within the 'IN' statement.

So, my question remains - how do I reference the current report group as the domain I've tried just using the table that the field comes from, but that doesn't work either - plus I can't see how that would give the correct answer.

Thanks






Re: Using DCount function in a Report group footer

Van T. Dinh

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 ...






Re: Using DCount function in a Report group footer

LizF@RMIT


Hi Van,

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]")

When I run the report I am prompted for a parameter value for 'Tasks' - which I don't know what to enter - clicking OK just gives an error for the count.

I have also tried it like this which produces no prompt but gives the same error result:
=DCount([Task ID],"Tasks","TaskID IN (SELECT DISTINCT [Task ID] FROM Tasks")

"Tasks" is my table.

I have now tried saving the report's query to a named query and refering to that as the domain as follows:
=DCount(["Task ID]","[Q_ServiceCount]","[Task ID] IN (SELECT DISTINCT [Task ID] FROM Tasks")
This produces same error (no prompt for param value).

I've also tried it like this:
=DCount("Task ID","Q_ServiceCount","[Task ID] IN (SELECT DISTINCT [Task ID] FROM Tasks")
Which also doesn't work - same error.

I have tried looking up the help on calculating a running sum but it doesn't say anything about excluding repeating values. You are correct in surmising that my use of the 'distinct' keyword is because there are duplicates.

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

Thanks




Re: Using DCount function in a Report group footer

Cringing Dragon

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.






Re: Using DCount function in a Report group footer

Cringing Dragon

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.