Form Input, Parameters, Report Based on Query

[DavidW]

I have a form where a user enters a start date and an end date in
textboxes.
A report is created from a query using the start date and the end date
entered
on the form as parameters. I created the report using the Wizard.

The form name is frmAdjustments.
The query name is qryAdjustments.
The report name is rptAdjustments.

How would I move the start date and the end date entered in the
textboxes
to the report so the date range is displayed?

Can I use code to place the values in the text boxes of the form in
textboxes or labels of the report?
For example: rptAdjustments.txtStartDate = frmAdjustments.txtStartDate.
The report is run when a View or Print button is selected. Could I
place code in those click events
to do what I need?

Or...
Can I get the start date and the end date from the parameters directly
from the query?
Is there a way to include the parameters in the SELECT statement to
enable the report
to access them? Here is the query:

PARAMETERS [Forms]![frmAdjustments].[txtStartDate] DateTime,
[Forms]![frmAdjustments].[txtEndDate] DateTime;
SELECT [Hoods].[Fabric], [Hoods].[Degree],
Sum([Transactions].[Adjustments])
AS Added,
Sum([Transactions].[AdjRemove]) AS Removed
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ((([Hoods].[Fabric])<>'ALL'
And ([Hoods].[Fabric])<>'Other') And (([Hoods].[Degree])<>'ALL'
And ([Hoods].[Degree])<>'Other'))
And ((([Transactions].[TDate])>=[Forms]![frmAdjustments].[txtStartDate]
And
[Transactions].[TDate])<[Forms]![frmAdjustments].[txtEndDate]+1))
GROUP BY [Hoods].[Fabric], [Hoods].[Degree];

I hope my question is clear. Basically, I want the two dates used as
parameters to display at the top of the report. The form, query, and
report all work correctly.

Thanks for any suggestions.

 

[Klatuu]

Use the Where argument of the OpenReport method to filter the data.
Here is an example assuming [SomeDate] is the field in the query that is the
record source of the report.

strWhere = "[SomeDate] Between #" & Me.StartDate & "# And #" & Me.EndDate &
"#"
Docmd.OpenReport "MyReportName", , ,strWhere

Now, if you want the selected dates to show up on the report, there are a
couple of ways you can do this. I don't know if versions XP or 2002 have the
OpenArgs property for reports, but I do know that 2000 does not, not does
2003 in 2000 format, but if you have 2003 in 2003 format, you can use the
OpenArgs argument to pass the dates to the report in the OpenArgs argument.
The only trick here is that you will need to convert both dates into one
string and then separate them back out in the Open event of the report.

Another way is make the control source of the text box where you want to
show the dates on the report refer to the form:

Forms!TheFormName!txtStartDate
Forms!TheFormName!txtEndDate

I have a form where a user enters a start date and an end date in
textboxes.
A report is created from a query using the start date and the end date
entered
on the form as parameters. I created the report using the Wizard.

The form name is frmAdjustments.
The query name is qryAdjustments.
The report name is rptAdjustments.

How would I move the start date and the end date entered in the
textboxes
to the report so the date range is displayed?

Can I use code to place the values in the text boxes of the form in
textboxes or labels of the report?
For example: rptAdjustments.txtStartDate = frmAdjustments.txtStartDate.
The report is run when a View or Print button is selected. Could I
place code in those click events
to do what I need?

Or...
Can I get the start date and the end date from the parameters directly
from the query?
Is there a way to include the parameters in the SELECT statement to
enable the report
to access them? Here is the query:

PARAMETERS [Forms]![frmAdjustments].[txtStartDate] DateTime,
[Forms]![frmAdjustments].[txtEndDate] DateTime;
SELECT [Hoods].[Fabric], [Hoods].[Degree],
Sum([Transactions].[Adjustments])
AS Added,
Sum([Transactions].[AdjRemove]) AS Removed
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ((([Hoods].[Fabric])<>'ALL'
And ([Hoods].[Fabric])<>'Other') And (([Hoods].[Degree])<>'ALL'
And ([Hoods].[Degree])<>'Other'))
And ((([Transactions].[TDate])>=[Forms]![frmAdjustments].[txtStartDate]
And
[Transactions].[TDate])<[Forms]![frmAdjustments].[txtEndDate]+1))
GROUP BY [Hoods].[Fabric], [Hoods].[Degree];

I hope my question is clear. Basically, I want the two dates used as
parameters to display at the top of the report. The form, query, and
report all work correctly.

Thanks for any suggestions.

 

[DavidW]

Thank you, Klatuu.

I went with the latter option. I put two text boxes on the report and
set the control sources to the text boxes on the form. I had tried that
before posting but got a syntax error that I could not find. You helped
me get the correct syntax. It works now.

Use the Where argument of the OpenReport method to filter the data.
Here is an example assuming [SomeDate] is the field in the query that is the
record source of the report.

strWhere = "[SomeDate] Between #" & Me.StartDate & "# And #" & Me.EndDate &
"#"
Docmd.OpenReport "MyReportName", , ,strWhere

Now, if you want the selected dates to show up on the report, there are a
couple of ways you can do this. I don't know if versions XP or 2002 have the
OpenArgs property for reports, but I do know that 2000 does not, not does
2003 in 2000 format, but if you have 2003 in 2003 format, you can use the
OpenArgs argument to pass the dates to the report in the OpenArgs argument.
The only trick here is that you will need to convert both dates into one
string and then separate them back out in the Open event of the report.

Another way is make the control source of the text box where you want to
show the dates on the report refer to the form:

Forms!TheFormName!txtStartDate
Forms!TheFormName!txtEndDate

I have a form where a user enters a start date and an end date in
textboxes.
A report is created from a query using the start date and the end date
entered
on the form as parameters. I created the report using the Wizard.

The form name is frmAdjustments.
The query name is qryAdjustments.
The report name is rptAdjustments.

How would I move the start date and the end date entered in the
textboxes
to the report so the date range is displayed?

Can I use code to place the values in the text boxes of the form in
textboxes or labels of the report?
For example: rptAdjustments.txtStartDate = frmAdjustments.txtStartDate.
The report is run when a View or Print button is selected. Could I
place code in those click events
to do what I need?

Or...
Can I get the start date and the end date from the parameters directly
from the query?
Is there a way to include the parameters in the SELECT statement to
enable the report
to access them? Here is the query:

PARAMETERS [Forms]![frmAdjustments].[txtStartDate] DateTime,
[Forms]![frmAdjustments].[txtEndDate] DateTime;
SELECT [Hoods].[Fabric], [Hoods].[Degree],
Sum([Transactions].[Adjustments])
AS Added,
Sum([Transactions].[AdjRemove]) AS Removed
FROM Hoods INNER JOIN Transactions ON
[Hoods].[ID]=[Transactions].[BoxID]
WHERE ((([Hoods].[Fabric])<>'ALL'
And ([Hoods].[Fabric])<>'Other') And (([Hoods].[Degree])<>'ALL'
And ([Hoods].[Degree])<>'Other'))
And ((([Transactions].[TDate])>=[Forms]![frmAdjustments].[txtStartDate]
And
[Transactions].[TDate])<[Forms]![frmAdjustments].[txtEndDate]+1))
GROUP BY [Hoods].[Fabric], [Hoods].[Degree];

I hope my question is clear. Basically, I want the two dates used as
parameters to display at the top of the report. The form, query, and
report all work correctly.

Thanks for any suggestions.