ADP - Adding Date Information used in a Filter to a Report Header.

[BigCat]

I am having access create monthly reports and I need the date as part of the header, but I can't use the NOW() function because these reports won't get printed until the first of the next month.

The Report comes up after the user inserts a beginning date and end date for the report and that is shown in the filter field in the property sheet of the form under the data tab.



I was wondering if there is a way to grab those dates from the filter and print them on the report.

[RuralGuy]

It is a readable property of the report. Try displaying it on your report and see what is there.

[BigCat]

That is my problem. I was pretty sure it was readable, I just don't know how to get it to print to the report to see if it works.

[RuralGuy]

Add a TextBox to the report with the ControlSource set to =Me.Filter and see what you get.

[BigCat]

I typed in =Me.Filter and it said Me is not a valid column name, so I changed me to the [DATE SOLD] column name because that is what it is filtering on.

I go back to report view then and the next box is only showing #Name?

[RuralGuy]

Change it to =Filter

[BigCat]

I did and now the text box comes up blank, so I went back and noticed that it isn't in filter but it is in Server Filter I am guessing this is another one of those things I have run across that makes access a little different when it hooks to SQL Server.

I did try typing =ServerFilter and =Server Filter, both of those also came up blank. Also when I am finished typing in the Control Source field and move to another it puts brackets up around Filter/ServerFilter like it thinks it is a field name.

[RuralGuy]

I suspect that is on the SQL Server and not actually available to the report which would make sense since the filtering *should* be accomplished on the server. Are you using the OpenReport to run the Report? If so then add the dates to the OpenArgs argument and getb them there.

[BigCat]

ok this is my line of code that actually opens the report:

DoCmd.OpenReport strReport, reportView, , strWhere

Do i put something in the empty param? or is it something else

[RuralGuy]

The syntax for OpenReport is actually:
expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
...and your report can see the OpenArgs argument: http://www.baldyweb.com/OpenArgs.htm

[BigCat]

So I could pass the dates in the two text boxes into the OpenArgs part and then somehow call them up and print them to the screen on the report?

[RuralGuy]

Absolutely. That's how I do it all of the time. Reports are just a bit different. I store the values in public variables in the Open Event and bind the Report TextBoxes to the variables.

[BigCat]

DoCmd.OpenReport strReport, reportView, , strWhere, , txtStartDate & ";" & txtEndDate

That is what I changed the open report to but you lost me talking about the open event and binding the text boxes. I am still trying to pick up Access and VBA.

[RuralGuy]

The example of Paul's I referred to is in the OnLOad event of a form and can reference controls on a form. I don't think you can reference the controls yet in the open event of the Report so simply store them in public variables.

[BigCat]

I added this to the report code:

Private Sub Report_Load()
Dim strOpenArgs() As String
If Not IsNull(Me.OpenArgs) Then
strOpenArgs = Split(Me.OpenArgs, ";")
Me.txtStartDate = strOpenArgs(0)
Me.txtEndDate = strOpenArgs(1)
Else
Me.txtOtherInfo = "Unknown"
End If
End Sub


And then I added Me.txtStartDate into the control source and then when I try to go to report view it tells me these are not valid column name.