DCount Nightmares

[firestorm52]

I'm attempting to summarize a report that was based on a query (qryDate) using the DCount function but I can't seem to get it to work correctly. First I'd like to count the number of incidents [Incident ID] so this is what I thought of:

=DCount("[Incident ID]","qryDate")

but for some reason I always get an error message. Can someone please help?

firestorm52
"Without struggle there can be no progress."
-someone mysterious

 

[firestorm52]

The error message says "#Error". I was able to use the Count Function to count how many total recordsets there are, but now I'm trying to figure something else out.

My query has a "Between [beginning date] And [ending date]" criteria associated with it and I think that's where the problem begins. Is it possible to use DCount to count the number of records associated with a field AND include a restraint on which records to count? I don't think so, but then again I'm still pretty green with Access.

Basically I've got a field in qryDate entitled "Service Type". The query asks the user for a beginning and ending date. At the bottom of the report for this query I want to include summary information that shows how many occurrences of each type of service type have occurred during the selected time period. Is this possible at all? Below is what I thought would work:

=DCount("[Incident ID]"."qryDate","[Service Type = 'Trash'")

Is this right?

 

[Tiro Cupidus]

Take the date parameters off "qryDate" and put them in the criteria of the DCount like this:

=DCount([Incident ID]","qryDate","[Service Type]='Trash' AND 
[yourDateField] Between #" & [beginning date] & "# And #" & [ending date] & "#")

edit
Closed a bracket.

 

[firestorm52]

Tiro thanx for the help! It worked out, but now the report will print all 151 pages and only give summary data on the dates specified. Is there anyway to make the report only print out all records that occur within the specified dates AND summarize all of the events that took place within the specified dates?

 

[mission2java_78]

Something tells me the design of your report is wrong. You can count incidents in the actual report and not the query. The query should have Between [start] AND [end].

Your report will then just query this data.

In the report footer of what ever you are grouping on do a:

=Sum([YourField])

As the control source of a text box.

 

[firestorm52]

mission, thanks for the advice, but I couldn't get it to work. I still don't understand why I'm not able to summarize the data for the required dates??!!

Could the problem be with the relationship between the query and main table? Or maybe I should use the ServiceID instead of Service Type in the DCount function?

Any ideas??

 

[mission2java_78]

mission, thanks for the advice, but I couldn't get it to work. I still don't understand why I'm not able to summarize the data for the required dates??!!

Could the problem be with the relationship between the query and main table? Or maybe I should use the ServiceID instead of Service Type in the DCount function?

Any ideas??

=Count([ServiceID])

will give you the count of the number of services.

Jon

 

[firestorm52]

Still not able to figure it out . . .

Ok I've tried everything that was suggested but I'm still not able to print out the report that I need. I'm not sure but I think the problem lies with using DCount on a query result. As soon as I remove the "Between/And" criteria, the formula works. This leads me to believe that DCount cannot work on a query with this type of criteria. Can anyone point me in the right direction to figuring out this problem? Anything would be greatly appreciated!!

Thanks
firestorm52

 

[firestorm52]

Figured it out, but now . . .

I was finally able to figure out a solution to my prior problem (Sum[Incident Type]), but now I'm attempting to post the dates that are being reviewed in the report heading.

I believe I need to create a subform that will ask for a beginning and ending date (instead of using Between [Beginning Date:] And [Ending Date:] in query). That way I will be able to use these dates as controls on the report. How does this sound?