Dcount Question

User avatar
Carol W.
3StarLounger
Posts: 390
Joined: 26 Jan 2010, 16:02
Location: Las Vegas, NV

Dcount Question

Post by Carol W. »

Please see attached zip file containing a very stripped down version of a much larger database. The report in question is rptHoursWithinDates. The recordsource for this report is qryVolunteerHoursByDate. The report needs to be run from form frm2-7-0 Get Time Related Reports Menu by entering any start/end dates in 2014. For example, start date is 1/1/2014 and end date is 12/31/2014.

The report footer shows subtotals by various fields. We did this the "old school" way, namely by putting logic in the Detail | Print event. This works perfectly. Before resorting to the "old school" approach, however, we tried using the Dcount function in a myriad of ways, to no avail.

Please tell us the best way to use the Dcount function to obtain the same results we have in the current version of the report (i.e. in the report footer). Or, if our approach is the only way to do this, please tell us that too.

Thanks in advance.
You do not have the required permissions to view the files attached to this post.
Carol W.

User avatar
Carol W.
3StarLounger
Posts: 390
Joined: 26 Jan 2010, 16:02
Location: Las Vegas, NV

Re: Dcount Question

Post by Carol W. »

Forgot to add to click button entitled "Hours Within Dates" to run report after entering from/to dates.

Sorry!
Carol W.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dcount Question

Post by HansV »

You don't need either code or DCount/DSum. You can calculate the counts and sums directly.
For example, the Control Source of Text45 can be:

=Abs(Sum([tbl648VolunteerSpecialRole]="M"))

and that of Text46:

=Nz(Abs(Sum(IIf([tbl648VolunteerSpecialRole]="M",[VolHrs]))),0)

See the attached version - I have created a copy of the report. You'll have to relink the tables in the frontend.
Toys4SmilesCode.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Carol W.
3StarLounger
Posts: 390
Joined: 26 Jan 2010, 16:02
Location: Las Vegas, NV

Re: Dcount Question

Post by Carol W. »

Thanks, Hans. This is a perfect solution.

Two additional questions:
1 - We see that you used the Abs function. The numbers are all positive so what is the reason Abs was used?

2 - We compared our numbers against yours. Yours are correct. Ours are off slightly. We initialize the counters in Report | Open and accumulated the totals in Detail | Print. Can you easily see what we're doing wrong?

Thanks again.
Carol W.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dcount Question

Post by HansV »

1. For example: the value of ([tbl648VolunteerSpecialRole]="M") is either True = -1 or False = 0. So each record for which the role is "M" (Monitor) contributes -1 to Sum([tbl648VolunteerSpecialRole]="M"). In other words, the sum is -1 times the number of records with "M". Abs removes the minus sign.

2. You used variables of type Long to keep track the hours. Long integers can only store whole numbers, so the fractional part of the hours is lost. By using Sum directly, the fractional part is included.
Best wishes,
Hans

User avatar
Carol W.
3StarLounger
Posts: 390
Joined: 26 Jan 2010, 16:02
Location: Las Vegas, NV

Re: Dcount Question

Post by Carol W. »

Perfect explanations as usual! I just changed the Dim statements for the hours fields to Double and our numbers now match.

:thankyou:
Carol W.