Dcount report prints correct data but prints once for each record it checks.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Conklin - 29 Oct 2004 05:19 GMT

This one really has me going. Probably something silly. I'm using
dcount for a report to determine the number of different types of
tests proctored in a semester.

My report is based on a parameter query which is the recordsource for
the report. The parameter is <=[Enter Date]. The query returns the
correct amounts upto the date entered (no need for "between" dates
here).

There are 8 textboxes with dcounts; 2 other boxes Sum some of these
values. Again the values are correct, but for every record (it
seems) that is checked, the report prints the report. So if the
query checks 480 records, the report values show up in the report 480
times.

paramQueryDate is the query with the <=[Enter Date] parameter.
TestInfo is a table the paramQueryDate is created from.

In the code examples below, I have used both the table name and the
query name between the middle quotes for the source. I have also
tried it with and without the [DateTestReturned] field included in the
Where condition.
[Enter Date] in the report header reflects the date entered correctly.

The dcounts in the textboxes are:

To Total Academic Makeups:
=DCount("[TestType] + [Returned] + [Expired] +
[DateTestReturned] ","paramQueryDate","[TestType]=1 AND
[Returned]=true AND [Expired] = false and [DateTestReturned] <=
[Enter Date]")

The other textbox dcounts are similar to this and return correct
values, but no matter how I set things up, the darned report still
prints once for what seems is every record checked.

Hiding duplicate values doesn't matter; lines and labels still show up
even though the textboxes do not. I have tried code combinations
without any recordsource identified for the report. Also tried using
the table and then the query as the recordsource (both return correct
values). The width of the report is only 6.625 inches (not too wide
but I believe the problem has more to do with the recordsource and
such).

Everything is right except for the way the report prints out. I'm
sure some will suggest there are more elegant ways to achieve these
results but this should be working. I don't get it. What am I
missing?
Mike

Reply to this Message

MGFoster - 29 Oct 2004 08:40 GMT

Not sure about some things mentioned in your post. But, I did notice
the DCount() is set up incorrectly. Try this instead:

=DCount("*","paramQueryDate","[TestType]=1 AND
[Returned]=true AND [Expired] = false and [DateTestReturned] <=
[Enter Date]")

Your first parameter should be a field name or the asterisk 'cuz the
explanation of DCount() is:

Count the 1st parameter, in the 2nd parameter, where the criteria for
counting is in the 3rd parameter.

Your first parameter:

[TestType] + [Returned] + [Expired] + [DateTestReturned]

would parse to something like this:

1 + (-1) + 0 + 38289

====

I can't understand the rest of your post, 'cuz it looks like your not
using expressions as they are generally used in this newsgroup.

Are you referring to Access Report objects as "reports," or are you
using the word report for something else?

"...for every record [...] that is checked, the report prints the
report." Huh?

How is a record checked? Is there VBA code in the report that tries to
print itself?

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> This one really has me going. Probably something silly. I'm using
> dcount for a report to determine the number of different types of
[quoted text clipped - 45 lines]
> missing?
> Mike

Reply to this Message

Mike Conklin - 30 Oct 2004 06:07 GMT

Thanks MG for responding to my post. I changed the first parameter to
an asterisk as you suggested but got the same results.

To clarify about the "report printing the report." The complete
reported values (in print preview) take up about 1/3 of the page. All
the values print twice on each page. So everything in the example below
will repeat twice on the same page. If there are 80 records (tests)
being evaluated, all the information below will appear twice on 40
pages. It looks like this and just repeats, in this case, 80 times:

Academic 40 Pending 0
Distance Ed 15 Expired 5
Placement 20 Total Tests Submitted 80
Total 75

The records are checked in the text boxes only by the dcount function,
nothing else, no vb code.

What I have now is:

paramQueryDate for the record source; <=[Enter Date] is the parameter.

dcounts for the textboxes, all of which are similar to:
=DCount("*","paramQueryDate","[TestType]=1 AND [Returned]=true AND
[Expired] = false and [DateTestReturned] <= [Enter Date]")

I have another report object that this is based on. It's nearly
identical except that there is no record source and no parameter query
(based on a table) and it works perfectly. It simply dcounts all the
records (there is no date option like the one I'm working on). When I
place the table it looks to in the record source, I get the same
repeating data like I'm getting now. If I remove the record source for
the report object you're helping me with, the report pops up immediately
with #error in the text boxes. This makes sense and I understand why
this occurs, dcount is not seeing the parameter it needs (the query does
not actually run from the 2nd dcount parameter).

Do I need a filter here that will present me with a date parameter? If
so, could you give me some direction?
Thanks again.

Reply to this Message

MGFoster - 30 Oct 2004 07:00 GMT

Can you post the SQL of the query? My guess about the query's columns:

SELECT [Type], Count(*) As TypeCount

or

SELECT [Type], Sum(Students) As TypeSum

My guess is: you are duplicating the results of the query w/ the
DCount() function as the controls' ControlSource, or, your query is
retrieving too many records 'cuz of bad design (joins are wrong, or you
should be using DISTINCTROW or GROUP BY).

Your report's Detail section should hold the [Type] and Count/Sum
fields. The "Total" field should be in a section footer.

If you want 2 columns on the report you will have to indicate that in
the File > Print Setup > Columns (tab) > Column Layout (section). Read
the Access help article "Set page setup options for printing" and
"Customize a multiple-column report" for more info on multi-column
reports.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Thanks MG for responding to my post. I changed the first parameter to
> an asterisk as you suggested but got the same results.
[quoted text clipped - 35 lines]
> Do I need a filter here that will present me with a date parameter? If
> so, could you give me some direction?

Reply to this Message

Mike Conklin - 30 Oct 2004 19:03 GMT

MG,

Here is the SQL code view of the paramQueryDate query object in my
Access 2000 database:

SELECT TestInfo.TestType, TestInfo.Returned, TestInfo.Expired,
TestInfo.DateTestReturned, TestInfo.SpecialtyType
FROM TestInfo
WHERE (((TestInfo.DateTestReturned)<=[Enter Date]));
The <=[Enter Date] parameter is in the Criteria line.

Repeating here for your convenience, the dcounts in the detail
section:
Academic, Distance Ed, and Placement textbox dcount codes are
identical except for [TestType]number:

=DCount("*","paramQueryDate","[TestType]=1 AND [Returned]=true
AND[Expired] = false and [DateTestReturned] <= [Enter Date]")

The textbox below these simply sums the total of the 3 textbox values.
There is nothing in the section footer. The textboxes with the
dcounts are just placed in the detail section.

I tried removing the [DateTestReturned] field from the 3rd parameter
in the dcount code above, but the data still repeats in the report.

I thought this would be an easy report to set up since it's identical
to the one that works perfectly. Didn't realize specifying a
parameter would be so problematic!

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 60 lines]
> > Do I need a filter here that will present me with a date parameter? If
> > so, could you give me some direction?

Reply to this Message

MGFoster - 31 Oct 2004 19:31 GMT

Ah... I see how you've set up your report: you've put TextBoxes in the
Detail section for each TestType and the TestType Name in the TextBoxes'
Label. Like this:

Label ControlSource
------- --------------
Academic =Dcount("*","paramQueryDate","TestType=1 And ... etc.
Distance Ed =Dcount("*","paramQueryDate","TestType=2 And ... etc.
Placement =Dcount("*","paramQueryDate","TestType=3 And ... etc.
Total =txtAcademic + txtDistanceEd + Placement

If so, it would be better to do away w/ this format and use a "usual"
report format.

Change the query to something like this:

Query1 (add the TestType Description):
PARAMETERS [Enter Date] DateTime;
SELECT I.TestType, T.TestTypeDescription, Returned, Expired,
DateTestReturned, SpecialtyType
FROM TestInfo AS I INNER JOIN TestTypes As T
ON I.TestType = T.TestType
WHERE DateTestReturned<=[Enter Date]

If this query is just for this report, then change it to something like
this:

Query2 (summarize everything in the query):
PARAMETERS [Enter Date] DateTime;
SELECT I.TestType, T.TestTypeDescription, Count(*) AS TypeCount,
Count(Returned) As ReturnedCount, Count(Expired) As ExpiredCount
FROM TestInfo AS I INNER JOIN TestTypes As T
ON I.TestType = T.TestType
WHERE DateTestReturned<=[Enter Date]
GROUP BY I.TestType, T.TestTypeDescription

This assumes there is a table "TestTypes" w/ columns
TestType (AutoNumber) and TestTypeDescription (Text)

with values like this:

TestType TestTypeDescription
1 Academic
2 Distance Ed
3 Placement
... etc. ...

For Query1 use the Report Wizard & select the Summary report option.

For Query2 use the Report Wizard and create a "normal" report.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> MG,
>
[quoted text clipped - 90 lines]
>>>Do I need a filter here that will present me with a date parameter? If
>>>so, could you give me some direction?

Reply to this Message

Mike Conklin - 02 Nov 2004 18:42 GMT

MG,
I think I figured this thing out. It turned out to be a pretty simple
solution.

First I used paramQueryDate as the recordsource with the <=[Enter
Date]parameter. In the textbox controls with the dcounts, I added
[DateTestReturned]<=[Enter Date] to the WHERE condition. I also
shortened the code up by using the asterisks as you suggested.

Then (this is the simple part), I moved everything out of the Detail
section into the Header. Voila. I have the correct data and it does
not repeat. The footer section will work as well. Makes sense now;
it doesn't repeat because it isn't in the Detail section. The other
report worked with dcounts in detail section because there is no
recordsource specified. Once a recordsource is specified in the
report properties, it apparently changes the way the detail section
reads the records.

Thanks MG for all your help. It helped me get reaquainteed with VB
and Sql in the search for this solution (it had been awhile).

Mike C.

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
[quoted text clipped - 49 lines]
>
> For Query2 use the Report Wizard and create a "normal" report.

Reply to this Message