Date Range on a Report

[cornishteeth]

I have a report that shows a chart that runs off a
parameter query using the date(Between [Start Date:
(mm/dd/yy)] And [Stop Date: (mm/dd/yy)]). The chart
comes up fine, but I wanted the date range to be
displayed on the page itself. I created an unbound
textbox and used (=[Enter Start Date: (mm/dd/yy)] & " -"
& [Enter Stop Date: (mm/dd/yy)] as the control source.
Theoretically, the date range should be displayed.
Right???? What comes up is #Name?. Any help here would
be appreciated.

Frank

 

[SydneyGeek]

What you need is 2 text boxes on a small form. [Start Date] and [End Date].
The form has a button which launches the query; the query uses the 2 textboxes as parameters. (eg Between [Forms]![MyForm]![Start Date] And [Forms]![MyForm]![End Date]).
Code for the button (the Click event) would be something like this:

Sub Button1_Click()
  MyForm.Hide 'Hide the form: still open so the data is available
  DoCmd.OpenReport "MyReportName",acViewPreview
End Sub

There is one trick you need: when the report launches, do not close the form or else you'll get errors (see code above). Instead, hide the form and set the report to close the form when you close the report. Something Like:
This goes in the Close event of the report

Sub MyReport_Close()
   DoCmd.Close acForm, "MyForm"

End Sub

If you need more detail, give a yell

Denis

 

[cornishteeth]

OK. I was a little vague on my last post, so a little
more in depth this time. I have a Report that contains a
chart. The chart is derived from a parameter query that
contains DATE, SumOfFootage, and EmployeeName. The
parameters come from the criteria in the Date field and
is Between [Start] And [Stop]. The chart is a bar graph
that contains EmployeeName on the Y-axis and SumOfFootage
on the X-axis. First question, how would I be able to
put the date range entered on the chart itself. I
attempted to put it on the report, but a few things went
wrong. First, #Name? kept appearing. I discovered the
record source was not set to my query. I changed it.
Then, I have a total of 22 of the same reports. Weird?(I
have 22 employees??) So, if I can make it to where it is
entered on the Chart itself or even on the report, that
would be delightful. Next, I want the names on the Y-
axis to go from the greatest value from the X-axis to the
least value. For example, if Bill had 100, Chuck had 2,
and Dean has 20, the chart would descend from Bill to
Dean to Chuck. Looked throughout the properties and
didn't seem to find it anywhere. Hope this is more in
detail. Thank you in advance for your help.

Frank

 

[SydneyGeek]

That's 2 of us who were a bit vague on their posts
You can put the data from the 2 text boxes that I mentioned on the report. One way:
1. Create a new LABEL on the report. Call it lblHeader
2. In the Open event of the report, create this expression:
lblHeader.Caption="Footage for " & [EmployeeName] & " between " & Format([forms]![myform]![DateFrom],"dd-mmm-yyyy") & " and " format([forms]![myform]![DateTo],"dd-mmm-yyyy")

That will adjust the banner as you preview the report.
(Modify names to suit)

As for the 22 names, there are 2 options:
1. You have set everything up OK but not filtered on names, so you get a page for each employee
2. You have 2 tables in the query, with no join, and you have the cartesian product (ie all posible combinations of employee record with report)

Once you correct for either of these options (check the Employee filter first) you should be right.

Denis

 

[cornishteeth]

Still no good. Here is the ROW SOURCE from my Chart....
SELECT [EMPLOYEE NAME],Sum([SumOfFOOTAGE]) AS [SumOfSumOfFOOTAGE] FROM [Totals_Level3] GROUP BY [EMPLOYEE NAME];

My Record Source for the report is set to Totals_Level3, the Filter On is set to NO, Order By On set to NO. I only have one table that my query feeds from.

I still have problems where 22 reports show up. They are virtually all the same. I created the report by the chart wizard and utilized the Totals_Level3 query. I put it on the report. Then within the details section, I changed my Record Source to Totals_Level3, and inputted a textbox on the actual report with =[Enter Start Date: (mm/dd/yy)] & " - " & [Enter Stop Date: (mm/dd/yy)]. So, I have a chart and a text box on a blank report. Everything works, but unfortunately, I get 22 reports. I thought maybe Force New Page, but that is set to none. I am learning bit by bit, but I am trying. Anything else that might cause a print out of 22 reports.

Frank

 

[cornishteeth]

I figured out the pareto format by adding ORDER BY to my SQL statement SELECT Totals_Level3.[EMPLOYEE NAME], Sum(Totals_Level3.SumOfFOOTAGE) AS SumOfSumOfFOOTAGE FROM Totals_Level3 GROUP BY Totals_Level3.[EMPLOYEE NAME] ORDER BY Sum(Totals_Level3.SumOfFOOTAGE);

For the 22 reports, I tried creating a macro PrintOut where it would only print page 1 of 1. I inserted it under OnPage. I get an error stating "The command or action PrintOut is not available now." Anyone have a clue what this means or a remedy to remove the 22 reports and only have a single report.

Frank

 

[SydneyGeek]

Still no good. Here is the ROW SOURCE from my Chart....
SELECT [EMPLOYEE NAME],Sum([SumOfFOOTAGE]) AS [SumOfSumOfFOOTAGE] FROM [Totals_Level3] GROUP BY [EMPLOYEE NAME];

My Record Source for the report is set to Totals_Level3, the Filter On is set to NO, Order By On set to NO. I only have one table that my query feeds from.

This is the issue: GROUP BY [EMPLOYEE NAME]
Your query has ALL of the employees. You need to filter the query to get just one employee, if you want only one report. Otherwise you will end up with a chart for each employee, giving you 22 reports.

Denis