When opening the query's, I do see Forms!Paramform!StartDate and
Forms!ParamForm!EndDate
Here are the two query's I have the date criteria entered into:
SELECT [production table].Date, [production table].Operator,
Count([production table].Date) AS CountOfDate, ([countofdate]*8) AS [Date
count]
FROM [production table]
GROUP BY [production table].Date, [production table].Operator
HAVING ((([production table].Date) Between forms!Paramform!StartDate And
forms!ParamForm!EndDate));
SELECT [Production sub-table].ID, [Production sub-table].Date, [production
table].Operator, [Production sub-table].[Job Type], Sum([Production
sub-table].Volume) AS SumOfVolume, [Production sub-table].[Start Time],
[Production sub-table].[End Time], [Trouble table (Operator) Query].DT,
IIf([End Time]<[Start Time],DateAdd("d",1,[End Time]),[End Time]) AS Expr1,
([Expr1]-[Start Time]) AS [Total time], IIf(IsNull([DT]),"0 ",[DT]) AS Expr2,
DateDiff("n",[Start Time],[Expr1]) AS [TT in minutes], [TT in
minutes]-[Expr2] AS Expr3, [Trouble table (Operator) Query].[ID for TR]
FROM ([Production sub-table] LEFT JOIN [Trouble table (Operator) Query] ON
([Production sub-table].Date = [Trouble table (Operator) Query].Date) AND
([Production sub-table].[ID for TR] = [Trouble table (Operator) Query].[ID
for TR])) INNER JOIN [production table] ON [Production sub-table].ID =
[production table].ID
GROUP BY [Production sub-table].ID, [Production sub-table].Date, [production
table].Operator, [Production sub-table].[Job Type], [Production
sub-table].[Start Time], [Production sub-table].[End Time], [Trouble table
(Operator) Query].DT, [Trouble table (Operator) Query].[ID for TR]
HAVING ((([Production sub-table].Date) Between
[forms]![Paramform]![StartDate] And [forms]![ParamForm]![EndDate]))
ORDER BY [Production sub-table].[Job Type];
When these query's are run, these are the Enter Parameter Values I see:
Forms!Paramform!StartDate
Forms!ParamForm!EndDate
fredg said:
When opening the query's, I am prompted for a start and end date. The
query's are retuning the correct data.
When opening the report without the Paramform, I am prompted for a start and
end date. The report returns the correct data.
Could the problem be with the command button on the paramform?
When creating the command button, a command button wizard opens. Do I close
the wizard and just code the on click to Me.Visable=False, select run macro,
preview report, etc?
:
On Tue, 6 Mar 2007 20:11:00 -0800, CMTH wrote:
When I run my report, there is no data populated.
The form opens correctly to enter the date and is closed when the report is
closed, so I believe that part of the coding is correct.
The command button is coded for Me.Visible = False
Should the command button be a macro that opens the report, or something else?
I have entered: Between forms!Paramform!StartDate and
forms!ParamForm!EndDate in my queries critera.
:
Thank you! That sequence works! All I had to do was alter the OnOpen event
on the report and the OK button on the form to mirror your commands, and
everything worked. There must have been something wrong with the macros I
was trying. Thanks so much!
:
I have a query to search a table of real estate properties. I use an unbound
form to enter all the search parameters at one time. I am attempting to
alter my final output from the tabular query datasheet to a nice-looking
property report.
The report is built with the query as the source. Now, I am trying to
develop the appropriate commands to be able to enter parameters into the form
and have Access return the results of the query in the format of the report.
I found a procedure for such a set-up in Microsoft Office Assistance, but I
am having trouble making it work (the instructions for the "OK" command
button for the form do not seem to work - the macro it's telling me to write
is designed to hide the form by using the SetValue function to alter the
form's "Visible" property, but when I test it, Access tells me the form has
no "Visible" property. I can see the "Visible" property under the properties
for the form's "Details," but not under the properties for the form itself.
I really have no idea what to make of that...I don't even know why I am
trying to hide the form).
BUT - I do not care whether or not I hide the form, I just want that "OK"
button to return a nice-looking report based the query parameters entered
into the form. I'm not sure if it's best to have the process start by
opening the report with an "OnOpen" series of commands or to have the process
start with clicking on the unbound form.
If anyone could walk me through the best way to do this, I would be
magnificently grateful. I am using Access 2003, database (mdb format).
Wil Loftis
You start the process by opening the form from the report open event.
This will filter a report according to the company selected, as well
as the date criteria wanted.
You can adapt this for whatever criteria you need.
Even though you are using macro's above, I'm going to assume you know
how to write an event procedure. If you don't I suggest you look it up
in Access Help before posting back.
Access Help + Answer Wizard + Create a Visual Basic procedure
Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"
Also add 2 unbound text controls.
Set their format to a valid date format.
Name them "StartDate" and "EndDate"
Add a Command Button to the form.
Code the button's click event:
Me.Visible = False
Name this form 'ParamForm'.
In the Report's Record Source [CompanyID] field criteria line write:
forms!ParamForm!FindCompany
As criteria in the query date field write:
Between forms!Paramform!StartDate and forms!ParamForm!EndDate
Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog
Code the report's Close event:
DoCmd.Close acForm, "ParamForm"
When ready to run the report, open the report.
The form will open and wait for the selection of the Company and the
entry of the starting and ending dates wanted.
Click the command button on the form and then report will run.
When the report closes, it will close the form.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
The command button on the form is coded
Me.Visible = False.
Try this.
Open your query (not the report).
You will be prompted to enter the start date and the end date.
Enter a valid set of dates for which you know you have records.
Does the query return the correct records.
If not, then the problem is in your query.
If you do get the correct records, then you'll have to double check
your report. If you have correctly done what my previous reply has
suggested, the report should run properly.
If you named the unbound controls on the ParamForm StartDate and
EndDate, the EXACT query prompts should have been
forms!ParamForm!StartDate
then
forms!ParamForm!EndDate
If your prompt was simply [StartDate] or [EndDate] (or similar) then
it's either not coming from the query (or else you didn't write the
criteria properly).
I'm also not sure that you have correctly entered the needed code in
the Form's Command Button event as well as the Report's Open and
Cliose events.
I'm going to assume you have never entered code before, so bear with
me.
Let's start with the ParamForm's command button.
After you add the command button, display it's property sheet.
Click on the event tab.
Click on the On Click event line.
Write
[Event Procedure]
on that line.
Note: If you have used the wizard, you are OK up to this point.
Then click on the little button with the 3 dots that appears on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines (If you have used the wizard to add the button,
you'll have more than 2 lines).
In any event delete any existing code between the
Private Sub CommandName_Click() line and the
End Sub line.
Then, between those 2 lines, write:
Me.Visible = False
Close the window and save the form.
Do the same in the Report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog
Make sure the OpenForm syntax contains the 5 commas.
Do the same for the Close event.
DoCmd.Close acForm, "ParamForm"
Close and save the code and report.
Try again.
If you still have a problem, copy and paste the EXACT query SQL into a
reply message, as well as the EXACT query prompts you are getting.
