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

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

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
http://mvp.support.microsoft.com/

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


"AccessNeophyte" wrote in message
oups.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

On Aug 18, 5:26 am, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
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/

"AccessNeophyte" wrote in message

oups.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

Liz

"AccessNeophyte" wrote in message
ups.com...
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
http://mvp.support.microsoft.com/

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

On Aug 20, 7:00 pm, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
Liz

"AccessNeophyte" wrote in message

ups.com...

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 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

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
http://mvp.support.microsoft.com/

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

"AccessNeophyte" wrote in message
oups.com...
On Aug 20, 7:00 pm, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
Liz

"AccessNeophyte" wrote in message

ups.com...

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 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

On Aug 22, 4:54 am, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
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/

"AccessNeophyte" wrote in message

oups.com...



On Aug 20, 7:00 pm, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
Liz

"AccessNeophyte" wrote in message

oups.com...

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 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- Hide quoted text -

- 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

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
http://mvp.support.microsoft.com/

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

"AccessNeophyte" wrote in message
oups.com...
On Aug 22, 4:54 am, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
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/

"AccessNeophyte" wrote in message

oups.com...



On Aug 20, 7:00 pm, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote:
Liz

"AccessNeophyte" wrote in message

oups.com...

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 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- Hide quoted text -

- 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