If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Report w/same number of duplicate identical pages as there are records
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 |
#3
|
|||
|
|||
Report w/same number of duplicate identical pages as there are records
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 |
#4
|
|||
|
|||
Report w/same number of duplicate identical pages as there are records
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/ |
#5
|
|||
|
|||
Report w/same number of duplicate identical pages as there are records
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 |
#6
|
|||
|
|||
Report w/same number of duplicate identical pages as there are records
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 |
#7
|
|||
|
|||
Report w/same number of duplicate identical pages as there are records
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 |
#8
|
|||
|
|||
Report w/same number of duplicate identical pages as there are records
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 |
Thread Tools | |
Display Modes | |
|
|