The Dreaded DCOUNT Function in a Report

[NeoPa]

Right Brian.

I'll need to look at this at home and that rules out tonight I'm afraid (as I'm out for hours).
Bump up the thread on Friday if I manage to overlook it again will you please.

[FishVal]

Hi, Brian.

The mdb you've provided makes little sense without linked Excel worksheet.

The record source for report control

=DCount("[Status]","ROAImagingQueWeb-LINKED","([Status]='Closed') AND ([DateReported] Between #09/16/07# AND #09/30/07#)")

seems to be Ok.

I can only guess that field name(s) may be misspelled or data type(s) may be incorrect.

You should provide linked Excel worksheet with several records sufficient for debug purposes. Replace all secure info with dummy values of the same data type.

Kind regards,
Fish

[NeoPa]

Brian, I get that you're trying to run a DCount() to populate a control somewhere and that you want the date range to be included. This range should be as specified by the operator when they run the report and enter the parameters for the underlying query.

1. Is the control within the report itself?
2. If so, why use DCount() (a Domain Aggregate function) as there are aggregation functions available to report controls anyway?

If you feel you do still need to use a separately defined DCount() call then the usual way of doing this is to create a form with controls on for the parameters. These controls are then referenced both in your underlying query and within the DCount() code (wherever that is).
Does this make sense?

[bmyers]

Well, if I understand you correctly, if the desired control is a result of evaluating the control data source of the report itself, then I'd have to say not exactly. The report is based on a query that requires the operator to provide the date range. In turn, said query looks at the linked table, "ROAImagingQueW eb-LINKED" which I thought I defined in the DCOUNT function. Which sounds like that may be my issue if I'm trying to look at a different entity (the table) but the report's source data is not the table but the query, which queries that same source table. Sheesh, that's confusing.

BUT it sounds like if I change the source data for the report to be the table itself, then I may not have to use the DCOUNT function at all, that I could use an aggregate function?? in the desired Report control (the CLOSED TICKET control in the Owner Footer group) to calculate the number of closed tickets per Owner. Although, if we change the source data to be the table rather than query, then I'm afraid I'd lose the ability to only parse out the data for an operator-supplied date range (which was the only point of the query anyway) within the Report. Unless there's another way to prompt for user input when the report is executed. Which do you think would be the best way?

And in response to "Fish", if it'd be easier for me to attach the linked spreadsheet, I'd be glad to. I just didn't want to create another post just to do an attachment.

AND THANK YOU GUYS for helping me still.

[NeoPa]

Brian,

Your post covers a lot of points so what I'll do is reply within your quoted post.

Well, if I understand you correctly, if the desired control is a result of evaluating the control data source of the report itself, then I'd have to say not exactly. The report is based on a query that requires the operator to provide the date range. In turn, said query looks at the linked table, "ROAImagingQueW eb-LINKED" which I thought I defined in the DCOUNT function. Which sounds like that may be my issue if I'm trying to look at a different entity (the table) but the report's source data is not the table but the query, which queries that same source table. Sheesh, that's confusing.

In fact the use of DCount() (or other domain aggregate functions) are MORE appropriate when dealing with recordset objects OTHER than the one which is the record source of the report. If this is the case, then it may well be appropriate to use it here. However, read on, as I think your best solution is to use a form.

BUT it sounds like if I change the source data for the report to be the table itself, then I may not have to use the DCOUNT function at all, that I could use an aggregate function?? in the desired Report control (the CLOSED TICKET control in the Owner Footer group) to calculate the number of closed tickets per Owner. Although, if we change the source data to be the table rather than query, then I'm afraid I'd lose the ability to only parse out the data for an operator-supplied date range (which was the only point of the query anyway) within the Report. Unless there's another way to prompt for user input when the report is executed. Which do you think would be the best way?

If you want to access data (say for other date ranges) within your report then you can't rely on aggregate functions within the report itself. However, if you are counting records which are processed within the report you can put "=Count([SomeControl])" in a header section (Group header or report header) which will do the job. You should understand that "[SomeControl]" means that is accessing a control in your report and not the underlying recordset directly. Only those items included as controls can be accessed like this.
If this is not appropriate for you then I would go with the form option. whereby you define the controls on the form and let it open the report for you (You'd need to code that in but we can help there if you choose that route).

And in response to "Fish", if it'd be easier for me to attach the linked spreadsheet, I'd be glad to. I just didn't want to create another post just to do an attachment.

Well, ... you could simply have attached it to this one. Tell you what, just attach it to the next one that you post. That'll be fine.

AND THANK YOU GUYS for helping me still.

No problems. Enjoy TheScripts :)

[bmyers]

The Dreaded DCOUNT Function in a Report ADDENDUM

After a longer absence than anticipated, I'm finally back trying to figure this thing out. I'm re-posting because even when I reply to the Original Post, I don't have the option to attach files. And this time the attached zipped database is not using the linked table as a source. At least the report in question isn't. I would still greatly appreciate any advice. And please feel free to combine this with the original post, like I said I wasn't given an option to attach files anymore. THANKS!!!

The Report in question is : "ROA Imaging QueWeb Eval Period Report (TEST COPY)"

You'll see under the Owner Footer that there are two controls where I have attempted to use the Count function to report how many Tickets for that Owner have a status of "Closed" and how many Tickets for that Owner have a Status of "Owned". That's where I'm having the difficulty. My goal is to have Access count and display the number of Closed vs. Owned tickets by Owner.

Also of importance, the underlying query of the report does prompt the operator for a beginning and end date but this might be causing a problem too because instead of using the Table itself (ROAImagingQueW eb-NOT LINKED"), I'm using a query so that my resulting data is limited based on user input for a date range. Maybe I just need to change the source data to be the table and not the query but then I'm stuck when I run the report because the report has to be date specific which is why I'm using the query to begin with.

I've tried so many combinations of formulas including those below, attempting to use the DCOUNT function as well.

=DCount("[Status]","Closed Query","[Owner]='"  & Owner & '")


=DCount("[Status]","Closed Query","[Owner]=' & Owner & _'")

Again, thanks so much for your help.

BGM

[bmyers]

Started another post to add Attachment.

The Dreaded DCOUNT Function in a Report ADDENDUM

[nico5038]

Did you try:

=DCount("*","[Closed Query]","Status='Closed' and [Owner]='"  & [Owner] & "'")

Because of spaces the [ and ] are needed. Best not to use spaces in (field)names :-)

Nic;o)

[bmyers]

Thanks for the pointer but alas it still didn't work. It gives a TOTAL of "0", which I'm not sure where it gets that number. Hmmmm. Thanks for the try though.

[NeoPa]

You needn't have started a new THREAD for this. It is possible to add an attachment to any of your posts using the Edit facility. Once in the POST edit screen you have attachment options.
Having read the first post of your new thread I'll merge it into this one for you now.

[nico5038]

Are you sure that the Status field holds "Closed" and isn't a lookup field (see field in tabledefinition ) holding another (e.g. numeric?) value...

Nic;o)

[bmyers]

Yep, it's just a text field in the original table.

And in regards to the new thread, I don't think I have the option to add an attachment just because the EDIT option doesn't appear for me on any of old posts except for a brief time right after I create a new thread. Otherwise, the only option buttons I see are "REPLY". Only when I create a new thread and then for a short time afterwards do I see the "EDIT" option which does allow me to add an attachment. For instance, even on this original thread, nowhere do I see an EDIT option on any of my posts, just REPLY. I'm not trying to disagree, I just don't have that visibility it appears. But I do appreciate the merge and the assistance.

[nico5038]

The Edit/Reply shows at the bottom right of your comment. You'll have to press this after adding the comment te be able to add an attachment.

The line:

=DCount("*","[Closed Query]","Status='Closed' and [Owner]='"  & [Owner] & "'")

should work, unless your Owner is a numeric ID, then use:

=DCount("*","[Closed Query]","Status='Closed' and [Owner]="  & [Owner])

And make sure both Status and Owner are fields in the [Closed Query]

Nic;o)

[nico5038]

OK, checked the attached database and found that the Dcount does work, but that your first Owner is Null.
This requires special coding, try:

=DCount("*","ROAImagingQueWeb-LINKED","[Status]='Closed' and nz([Owner],'')='" & [Owner] & "'")

Nic;o)

[bmyers]

Well, I think we're getting closer but I'm still getting odd results and don't know how it's calculating the number.

For instance, if you run the Report "ROA Imaging QueWeb Eval Period Report (TEST COPY)" and use a start date of 05/01/07 and an End Date of 08/31/07, you'll see the report period is for that date range only, which that part seems to work. However, if you are look at one of the "Owners", we'll use Mike Eastham for an example in this case. My goal is not only to report how TOTAL (including Owned and Closed) tickets but also how many "Closed" tickets he had during that time. Currently, with the most recent formula suggested, it's showing that Mike had 90 Closed tickets but if you look at the report you can see that he had only 65 tickets. So, i'm not sure where this number is coming from at this point. What do you think? I attached a snapshot of the report.