Report w/same number of duplicate identical pages as there are records

[AccessNeophyte]

Greetings all,

I've been researching this in this Group, and some answers have come
close, but ... no cigar. So let's see if I can explain this
correctly.

I've got a report that has 2 subreports in the Detail section, each of
which has it's own query. When I simply run the report (without a
RecordSource), it prints both subreports nicely on one page. It
prompts me twice for [Starting Date] and [Ending Date] though - once
for each subreport/subquery. That's not the major thing, though.

In the ReportHeader, I refer to the [Starting Date] twice, once for
month "mmmm" and once for year "yyyy" Also, in the PageFooter, I
refer to both dates with this string, as a date range:
=(Format([Starting Date],"mm/dd/yyyy")) & " " & "to" & " " &
(Format([Ending Date],"mm/dd/yyyy")). As an aside (possibly relevant),
if I run the report without a RecordSource, as above, the ReportHeader
and PageFooter references return #Name?

The problem is this:
1) If I use either subreport query as the ControlSource for any of
these date controls on the Report, like this: {=Format([BMC Monthly
Bed Days SubQuery - Bed Days]!Expr1,"mmmm")}, I get the same number of
duplicate pages of the same report as the number of records that the
subreport query returns. Subreport returns 37 records, I get 37 pages
of the same report. All 37 pages have the correct dates and formats
in the ReportHeader and PageFooter references, though.
2) If I create a query as a RecordSource for the Report, using the
same prompts as the subreport queries, [Starting Date] and [Ending
Date], with the master table as the query's source, I get the same
number of duplicate pages of the same report as the number of records
in the master table (164 records, 164 pages).
3) If I use the master table as the RecordSource for the report, I
get the same result as in #2.
4) But with 2 and 3, the ReportHeader and PageFooter references
return #Name?

Any idea what I'm doing wrong? Would I have more success with a
Dialog Box (if I can figure out how to use one)?

Thanks in advance!!!
Liz

 

[Jeff Boyce]

Liz

This may be what you meant by a "dialog box".

One way to "pass" a date into a report is to use a form. Create a textbox
on a form to hold your values for [Start Date] and [End Date]. Add a
command button that opens the report.

Now modify each of the queries that underlie your report so that they "point
to" the two controls on the form to get their date range data. If, as in
your main report, there is no underlying query, then, in the report, in the
OnFormat event, "point to" the two controls on the form to get/put your
values in your report's controls.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Greetings all,

I've been researching this in this Group, and some answers have come
close, but ... no cigar. So let's see if I can explain this
correctly.

I've got a report that has 2 subreports in the Detail section, each of
which has it's own query. When I simply run the report (without a
RecordSource), it prints both subreports nicely on one page. It
prompts me twice for [Starting Date] and [Ending Date] though - once
for each subreport/subquery. That's not the major thing, though.

In the ReportHeader, I refer to the [Starting Date] twice, once for
month "mmmm" and once for year "yyyy" Also, in the PageFooter, I
refer to both dates with this string, as a date range:
=(Format([Starting Date],"mm/dd/yyyy")) & " " & "to" & " " &
(Format([Ending Date],"mm/dd/yyyy")). As an aside (possibly relevant),
if I run the report without a RecordSource, as above, the ReportHeader
and PageFooter references return #Name?

The problem is this:
1) If I use either subreport query as the ControlSource for any of
these date controls on the Report, like this: {=Format([BMC Monthly
Bed Days SubQuery - Bed Days]!Expr1,"mmmm")}, I get the same number of
duplicate pages of the same report as the number of records that the
subreport query returns. Subreport returns 37 records, I get 37 pages
of the same report. All 37 pages have the correct dates and formats
in the ReportHeader and PageFooter references, though.
2) If I create a query as a RecordSource for the Report, using the
same prompts as the subreport queries, [Starting Date] and [Ending
Date], with the master table as the query's source, I get the same
number of duplicate pages of the same report as the number of records
in the master table (164 records, 164 pages).
3) If I use the master table as the RecordSource for the report, I
get the same result as in #2.
4) But with 2 and 3, the ReportHeader and PageFooter references
return #Name?

Any idea what I'm doing wrong? Would I have more success with a
Dialog Box (if I can figure out how to use one)?

Thanks in advance!!!
Liz

 

[AccessNeophyte]

Liz

This may be what you meant by a "dialog box".

One way to "pass" a date into a report is to use a form. Create a textbox
on a form to hold your values for [Start Date] and [End Date]. Add a
command button that opens the report.

Now modify each of the queries that underlie your report so that they "point
to" the two controls on the form to get their date range data. If, as in
your main report, there is no underlying query, then, in the report, in the
OnFormat event, "point to" the two controls on the form to get/put your
values in your report's controls.

Good luck!

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Greetings all,

I've been researching this in this Group, and some answers have come
close, but ... no cigar. So let's see if I can explain this
correctly.

I've got a report that has 2 subreports in the Detail section, each of
which has it's own query. When I simply run the report (without a
RecordSource), it prints both subreports nicely on one page. It
prompts me twice for [Starting Date] and [Ending Date] though - once
for each subreport/subquery. That's not the major thing, though.

In the ReportHeader, I refer to the [Starting Date] twice, once for
month "mmmm" and once for year "yyyy" Also, in the PageFooter, I
refer to both dates with this string, as a date range:
=(Format([Starting Date],"mm/dd/yyyy")) & " " & "to" & " " &
(Format([Ending Date],"mm/dd/yyyy")). As an aside (possibly relevant),
if I run the report without a RecordSource, as above, the ReportHeader
and PageFooter references return #Name?

The problem is this:
1) If I use either subreport query as the ControlSource for any of
these date controls on the Report, like this: {=Format([BMC Monthly
Bed Days SubQuery - Bed Days]!Expr1,"mmmm")}, I get the same number of
duplicate pages of the same report as the number of records that the
subreport query returns. Subreport returns 37 records, I get 37 pages
of the same report. All 37 pages have the correct dates and formats
in the ReportHeader and PageFooter references, though.
2) If I create a query as a RecordSource for the Report, using the
same prompts as the subreport queries, [Starting Date] and [Ending
Date], with the master table as the query's source, I get the same
number of duplicate pages of the same report as the number of records
in the master table (164 records, 164 pages).
3) If I use the master table as the RecordSource for the report, I
get the same result as in #2.
4) But with 2 and 3, the ReportHeader and PageFooter references
return #Name?

Any idea what I'm doing wrong? Would I have more success with a
Dialog Box (if I can figure out how to use one)?

Thanks in advance!!!
Liz- Hide quoted text -

- Show quoted text -

Thanks so much Jeff,

It's good to know I was on the right track!

The Dialog box is giving me problems, though.
**************************************************************************************
One way to "pass" a date into a report is to use a form. Create a
textbox
on a form to hold your values for [Start Date] and [End Date]. Add a
command button that opens the report.
**************************************************************************************
I've created the form, as you outlined here.

**************************************************************************************
Now modify each of the queries that underlie your report so that they
"point
to" the two controls on the form to get their date range data.
**************************************************************************************
I did that. This is the SQL view of the result:

SELECT [BMC Client Data Table].BMC_ID, [BMC Client Data Table].Bed,
[BMC Client Data Table].FirstName, [BMC Client Data Table].LastName,
[BMC Client Data Table].AdmitDate, [BMC Client Data
Table].DC_DischargeDate, Forms![Start & End Date Dialog]!StartingDate
AS Expr1, Format([Expr1],"mmmm") AS Expr2, Format([Expr1],"yyyy") AS
Expr4, IIf(([AdmitDate]>Forms![Start & End Date Dialog]!EndingDate) Or
([DC_DischargeDate]<Forms![Start & End Date Dialog]!StartingDate),
0,DateDiff("d",IIf([AdmitDate]>Forms![Start & End Date Dialog]!
StartingDate,[AdmitDate],Forms![Start & End Date Dialog]!
StartingDate),IIf([DC_DischargeDate]<Forms![Start & End Date Dialog]!
EndingDate,[DC_DischargeDate],Forms![Start & End Date Dialog]!
EndingDate))) AS Expr3, Forms![Start & End Date Dialog]!EndingDate AS
Expr5
FROM [BMC Client Data Table]
WHERE ((([BMC Client Data Table].AdmitDate)<[Forms]![Start & End Date
Dialog]![EndingDate]) AND (([BMC Client Data Table].DC_DischargeDate)
Is Null)) OR ((([BMC Client Data Table].AdmitDate)<[Forms]![Start &
End Date Dialog]![EndingDate]) AND (([BMC Client Data
Table].DC_DischargeDate)>[Forms]![Start & End Date Dialog]!
[StartingDate]))
ORDER BY [BMC Client Data Table].Bed, [BMC Client Data
Table].AdmitDate;
**************************************************************************************
When I try to run the query (or the report), I get 2 pop-up dialogs
asking for
"Forms!Start & End Date Dialog!StartingDate"
and
"Forms!Start & End Date Dialog!EndingDate"

sigh...

Am I a lost cause?

Thanks for any help you can give me.
Liz

 

[Jeff Boyce]

Liz

When I try to run the query (or the report), I get 2 pop-up dialogs
asking for
"Forms!Start & End Date Dialog!StartingDate"
and
"Forms!Start & End Date Dialog!EndingDate"

sigh...

Am I a lost cause?

Thanks for any help you can give me.
Liz

When I get messages like those, it means I've misspelled something (or more
likely, spelled it differently in different places).

The fact that there are no square brackets in the messages around that form
name containing both spaces and an ampersand special character causes me
concern.

If you've typed (hand entered) the path, try opening the query in design
view and using the wizard (magic wand) to select the form, form name, form
control -- Access spells it exactly as it has it.

If that still fails, consider changing the name of the form to leave out
spaces and ampersand.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[AccessNeophyte]

Liz





When I get messages like those, it means I've misspelled something (or more
likely, spelled it differently in different places).

The fact that there are no square brackets in the messages around that form
name containing both spaces and an ampersand special character causes me
concern.

If you've typed (hand entered) the path, try opening the query in design
view and using the wizard (magic wand) to select the form, form name, form
control -- Access spells it exactly as it has it.

If that still fails, consider changing the name of the form to leave out
spaces and ampersand.

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Thanks Jeff,

OK, I've changed the Dialog Box name to:
StartDateEndDateDialog

And I used the wand (Expression Builder, right?) to select the form in
the left section, selected the StartingDate field in the <form>
section in the middle. Where I think I'm running into a problem is
the section on the right, which I think contains the form's
properties. Which of those properties do I select? OnClick, maybe?
OnEnter?

What's next?

Sincere regards,
Liz

 

[Jeff Boyce]

None!

What you want the expression builder wizard to do is place the (value of)
Form!YourForm!YourControl into the Selection Criterion "cell". Twice
actually, because you have two controls, and you'd be using the Between ...
And ... expression.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[AccessNeophyte]

None!

What you want the expression builder wizard to do is place the (value of)
Form!YourForm!YourControl into the Selection Criterion "cell". Twice
actually, because you have two controls, and you'd be using the Between ...
And ... expression.

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/











- Show quoted text -

sigh...

I've done that, but when I try to run the query, it still prompts me
with the 2 popups:
"Forms!StartDateEndDateDialog!StartingDate"
and
"Forms!StartDateEndDateDialog!EndingDate"

Here's the SQL:
***************************************
SELECT [BMC Client Data Table].BMC_ID, [BMC Client Data Table].Bed,
[BMC Client Data Table].FirstName, [BMC Client Data Table].LastName,
[BMC Client Data Table].AdmitDate, [BMC Client Data
Table].DC_DischargeDate, Forms!StartDateEndDateDialog!StartingDate AS
Expr1, Format([Expr1],"mmmm") AS Expr2, Format([Expr1],"yyyy") AS
Expr4, IIf(([AdmitDate]>Forms!StartDateEndDateDialog!EndingDate) Or
([DC_DischargeDate]<Forms!StartDateEndDateDialog!StartingDate),
0,DateDiff("d",IIf([AdmitDate]>Forms!StartDateEndDateDialog!
StartingDate,[AdmitDate],Forms!StartDateEndDateDialog!
StartingDate),IIf([DC_DischargeDate]<Forms!StartDateEndDateDialog!
EndingDate,[DC_DischargeDate],Forms!StartDateEndDateDialog!
EndingDate))) AS Expr3, Forms!StartDateEndDateDialog!EndingDate AS
Expr5
FROM [BMC Client Data Table]
WHERE ((([BMC Client Data Table].AdmitDate)<[Forms]!
[StartDateEndDateDialog]![EndingDate]) AND (([BMC Client Data
Table].DC_DischargeDate) Is Null)) OR ((([BMC Client Data
Table].AdmitDate)<[Forms]![StartDateEndDateDialog]![EndingDate]) AND
(([BMC Client Data Table].DC_DischargeDate)>[Forms]!
[StartDateEndDateDialog]![StartingDate]))
ORDER BY [BMC Client Data Table].Bed, [BMC Client Data
Table].AdmitDate;
***************************************

any ideas?

Thanks again!
Liz

 

[Jeff Boyce]

If this were mine, I'd back up a few steps and try the query out with
"hard-coded" date values in place of the parameters. If that query works,
it seems likely the "spelling" of the reference is somehow off ... OR, if
the form in which the date values are placed is NOT open, the query won't be
able to find them.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

None!

What you want the expression builder wizard to do is place the (value of)
Form!YourForm!YourControl into the Selection Criterion "cell". Twice
actually, because you have two controls, and you'd be using the Between ....
And ... expression.

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/







(or
more
that
form
name,
form









- Show quoted text -

sigh...

I've done that, but when I try to run the query, it still prompts me
with the 2 popups:
"Forms!StartDateEndDateDialog!StartingDate"
and
"Forms!StartDateEndDateDialog!EndingDate"

Here's the SQL:
***************************************
SELECT [BMC Client Data Table].BMC_ID, [BMC Client Data Table].Bed,
[BMC Client Data Table].FirstName, [BMC Client Data Table].LastName,
[BMC Client Data Table].AdmitDate, [BMC Client Data
Table].DC_DischargeDate, Forms!StartDateEndDateDialog!StartingDate AS
Expr1, Format([Expr1],"mmmm") AS Expr2, Format([Expr1],"yyyy") AS
Expr4, IIf(([AdmitDate]>Forms!StartDateEndDateDialog!EndingDate) Or
([DC_DischargeDate]<Forms!StartDateEndDateDialog!StartingDate),
0,DateDiff("d",IIf([AdmitDate]>Forms!StartDateEndDateDialog!
StartingDate,[AdmitDate],Forms!StartDateEndDateDialog!
StartingDate),IIf([DC_DischargeDate]<Forms!StartDateEndDateDialog!
EndingDate,[DC_DischargeDate],Forms!StartDateEndDateDialog!
EndingDate))) AS Expr3, Forms!StartDateEndDateDialog!EndingDate AS
Expr5
FROM [BMC Client Data Table]
WHERE ((([BMC Client Data Table].AdmitDate)<[Forms]!
[StartDateEndDateDialog]![EndingDate]) AND (([BMC Client Data
Table].DC_DischargeDate) Is Null)) OR ((([BMC Client Data
Table].AdmitDate)<[Forms]![StartDateEndDateDialog]![EndingDate]) AND
(([BMC Client Data Table].DC_DischargeDate)>[Forms]!
[StartDateEndDateDialog]![StartingDate]))
ORDER BY [BMC Client Data Table].Bed, [BMC Client Data
Table].AdmitDate;
***************************************

any ideas?

Thanks again!
Liz