Open report with limited date-range as tought by A.Browne

M

Mishanya

I've built a form wich serves as "limiter-opener" for MyReport using:
1) A. Browne's Popup Calendar utility (form and modul) to select StartDate
and EndDate
2) A. Browne code from http://allenbrowne.com/casu-08.html to open MyReport
filtered for the selected dates.

The code works fine while opening MyReport directly:

Private Sub btnOpenMyReport_Click()

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "MyReport"
strDateField = "[Date]"
lngView = acViewPreview

If IsDate(Me.StartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.StartDate,
strcJetDate) & ")"
End If
If IsDate(Me.EndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.EndDate + 1,
strcJetDate) & ")"
End If

DoCmd.OpenReport strReport, lngView, , strWhere

End Sub

Now I need to tune this, because MyReport is actually subreport of
MainReport (as a part of the MainReport it should present transactions
limited to the date-range).

I've changed the following part:

strReport = "MainReport"
strDateField = "Reports![MainReport]![MyReport].Report![Date]"

but the result is blank MainReport.

Is it possible to filter the date-range of subreport by coding in such a
manner a control-button used for opening the MainReport?
If it is, please correct my code. Also please, show me how to add to the
code additional filter for ClientID (the form wich limits and opens the
MainReport has ClientID control).

Thanks in advance.
 
K

Ken Sheridan

The WhereCondition argument of the OpenReport method filters the parent
report, not the subreport. I'd suggest basing the subreport on a query which
references the StartDate and EndDate controls on your form as parameters, e.g.

PARAMETERS
Forms!YourForm!StartDate DATETIME,
Forms!YourForm!EndDate DATETIME;
SELECT *
FROM Transactions
WHERE ([Date] >= Forms!YourForm!StartDate
OR Forms!YourForm!StartDate IS NULL)
AND ([Date] < DATEADD("d", 1, Forms!YourForm!EndDate)
OR Forms!YourForm!EndDate IS NULL);

Note that parameters of date data type should always be declared to avoid
the parameter values being mistakenly interpreted as arithmetical expressions
and giving the wrong result.

You then simply need to open the report filtered to the client, e.g.

DoCmd.OpenReport "MainReport", _
View:=acViewPreview, _
WhereCondition:="ClientID = " & Me.ClientID

This assumes ClientID is a number data type; if its text data type wrap the
value in quotes characters:

WhereCondition:="ClientID = """ & Me.ClientID & """"

By testing in the query for each parameter being NULL this optionalizes them
in effect, i.e. if neither have values the rows returned will not be
restricted to any date range, if the start date only has a value then rows on
or after that date will be returned, and if the end data only has a value
rows on or before that date will be returned.

Be sure that the form remains open while the report is open. If you wish to
close the form automatically do so with code in the report's Close event
procedure.

BTW I'd avoid using Date as a column name; it’s the name of a built in
function and could cause confusion. If you do use it be sure to wrap it in
square brackets, [Date], when referencing it in code or SQL, but its better
to use a more specific bane such as TransactionDate.

Ken Sheridan
Stafford, England

Mishanya said:
I've built a form wich serves as "limiter-opener" for MyReport using:
1) A. Browne's Popup Calendar utility (form and modul) to select StartDate
and EndDate
2) A. Browne code from http://allenbrowne.com/casu-08.html to open MyReport
filtered for the selected dates.

The code works fine while opening MyReport directly:

Private Sub btnOpenMyReport_Click()

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "MyReport"
strDateField = "[Date]"
lngView = acViewPreview

If IsDate(Me.StartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.StartDate,
strcJetDate) & ")"
End If
If IsDate(Me.EndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.EndDate + 1,
strcJetDate) & ")"
End If

DoCmd.OpenReport strReport, lngView, , strWhere

End Sub

Now I need to tune this, because MyReport is actually subreport of
MainReport (as a part of the MainReport it should present transactions
limited to the date-range).

I've changed the following part:

strReport = "MainReport"
strDateField = "Reports![MainReport]![MyReport].Report![Date]"

but the result is blank MainReport.

Is it possible to filter the date-range of subreport by coding in such a
manner a control-button used for opening the MainReport?
If it is, please correct my code. Also please, show me how to add to the
code additional filter for ClientID (the form wich limits and opens the
MainReport has ClientID control).

Thanks in advance.
Click to expand...
 
M

Mishanya

I'm a new user, so every time I copy someone's tip, tune it up with my DB
control-names, switch to the view mode, press the button - and it works!!!
makes me gonna WOW!
Thank You very much, Ken!
BTW - surely I don't use words like Date as fieldnames - in my case it's
"Taarikh" wich is latin transcryption for "date" in Hebrew.

Ken Sheridan said:
The WhereCondition argument of the OpenReport method filters the parent
report, not the subreport. I'd suggest basing the subreport on a query which
references the StartDate and EndDate controls on your form as parameters, e.g.

PARAMETERS
Forms!YourForm!StartDate DATETIME,
Forms!YourForm!EndDate DATETIME;
SELECT *
FROM Transactions
WHERE ([Date] >= Forms!YourForm!StartDate
OR Forms!YourForm!StartDate IS NULL)
AND ([Date] < DATEADD("d", 1, Forms!YourForm!EndDate)
OR Forms!YourForm!EndDate IS NULL);

Note that parameters of date data type should always be declared to avoid
the parameter values being mistakenly interpreted as arithmetical expressions
and giving the wrong result.

You then simply need to open the report filtered to the client, e.g.

DoCmd.OpenReport "MainReport", _
View:=acViewPreview, _
WhereCondition:="ClientID = " & Me.ClientID

This assumes ClientID is a number data type; if its text data type wrap the
value in quotes characters:

WhereCondition:="ClientID = """ & Me.ClientID & """"

By testing in the query for each parameter being NULL this optionalizes them
in effect, i.e. if neither have values the rows returned will not be
restricted to any date range, if the start date only has a value then rows on
or after that date will be returned, and if the end data only has a value
rows on or before that date will be returned.

Be sure that the form remains open while the report is open. If you wish to
close the form automatically do so with code in the report's Close event
procedure.

BTW I'd avoid using Date as a column name; it’s the name of a built in
function and could cause confusion. If you do use it be sure to wrap it in
square brackets, [Date], when referencing it in code or SQL, but its better
to use a more specific bane such as TransactionDate.

Ken Sheridan
Stafford, England

Mishanya said:
I've built a form wich serves as "limiter-opener" for MyReport using:
1) A. Browne's Popup Calendar utility (form and modul) to select StartDate
and EndDate
2) A. Browne code from http://allenbrowne.com/casu-08.html to open MyReport
filtered for the selected dates.

The code works fine while opening MyReport directly:

Private Sub btnOpenMyReport_Click()

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "MyReport"
strDateField = "[Date]"
lngView = acViewPreview

If IsDate(Me.StartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.StartDate,
strcJetDate) & ")"
End If
If IsDate(Me.EndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.EndDate + 1,
strcJetDate) & ")"
End If

DoCmd.OpenReport strReport, lngView, , strWhere

End Sub

Now I need to tune this, because MyReport is actually subreport of
MainReport (as a part of the MainReport it should present transactions
limited to the date-range).

I've changed the following part:

strReport = "MainReport"
strDateField = "Reports![MainReport]![MyReport].Report![Date]"

but the result is blank MainReport.

Is it possible to filter the date-range of subreport by coding in such a
manner a control-button used for opening the MainReport?
If it is, please correct my code. Also please, show me how to add to the
code additional filter for ClientID (the form wich limits and opens the
MainReport has ClientID control).

Thanks in advance.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reports filtering 1
Filtering 3
Combine combo boxes with date range 16
Filter Report by Form 2
Open Report with the parameter 1
Help with WHERE filter on opening report from dialog 5
Noob VB code, Need help with Passing multiSelect Listbox 11
Pass criteria to open report for selected ClientID 5

Top