Filter question

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

Hi All,


I do try to open a report with a command button from a form, the report should be filtered depending on the users choice on the form.

FORM

Private Sub cmdPrint_Click()
Dim stgFilter As String

'Filter not transfered ??????????
stgFilter = "([Type] = '" & Me.lstProject & "')"
DoCmd.OpenReport "rptPojectOffshore", acViewPreview, stgFilter

End Sub

When opening the report the filter seems not to work? Even the first attempt to print the filter gives no (blanc) return.

REPORT

Private Sub Report_Open(Cancel As Integer)

'On Error Resume Next

Debug.Print "Filter: "; Me.filter

End Sub


Who can I get my Filter working?

I have never realy understood the diffrence between using a filter or the WHERE clause.


Thx a lot,
Ludovic
 
Vsn said:
I do try to open a report with a command button from a form, the
report should be filtered depending on the users choice on the form.

FORM

Private Sub cmdPrint_Click()
Dim stgFilter As String

'Filter not transfered ??????????
stgFilter = "([Type] = '" & Me.lstProject & "')"
DoCmd.OpenReport "rptPojectOffshore", acViewPreview, stgFilter

End Sub

When opening the report the filter seems not to work? Even the first
attempt to print the filter gives no (blanc) return.

REPORT

Private Sub Report_Open(Cancel As Integer)

'On Error Resume Next

Debug.Print "Filter: "; Me.filter

End Sub


Who can I get my Filter working?

I have never realy understood the diffrence between using a filter or
the WHERE clause.
Click to expand...

That lack of understanding is the issue. Move your stgFilter variable into the WHERE argument and out of the FILTER argument. The filter argument is meant to hold the name of a query to be used instead of the report's normal RecordSource.

In all my years of using Access I have never once used the Filter argument. Only the WHERE argument. It is a bit confusing because the WHERE argument actually "filters" the report and you will see it in the report's filter property. The same does not happen when using the FILTER argument.
 
A different approach would be to have a query that reflects the same
record source as your form only add criteria to an ID field in the
query so that it reflects the current record on the form ie:
forms!myform.txt_ID

Simply base your report on this new query and open with your command
button

Hi All,


I do try to open a report with a command button from a form, the report should be filtered depending on the users choice on the form.

FORM

Private Sub cmdPrint_Click()
Dim stgFilter As String

'Filter not transfered ??????????
stgFilter = "([Type] = '" & Me.lstProject & "')"
DoCmd.OpenReport "rptPojectOffshore", acViewPreview, stgFilter

End Sub

When opening the report the filter seems not to work? Even the first attempt to print the filter gives no (blanc) return.

REPORT

Private Sub Report_Open(Cancel As Integer)

'On Error Resume Next

Debug.Print "Filter: "; Me.filter

End Sub


Who can I get my Filter working?

I have never realy understood the diffrence between using a filter or the WHERE clause.


Thx a lot,
Ludovic


------=_NextPart_000_0170_01C729F5.418FFBF0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 2449

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.3020" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Arial size=2>Hi All,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><BR><FONT face=Arial size=2>I do try to open a report with a command button
from a form, the report should be filtered depending on the users choice on the
form.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>FORM</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Private Sub cmdPrint_Click()<BR>&nbsp;&nbsp;&nbsp;
Dim stgFilter As String<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; 'Filter not
transfered ??????????<BR>&nbsp;&nbsp;&nbsp; stgFilter = "([Type] = '" &amp;
Me.lstProject &amp; "')"<BR>&nbsp;&nbsp;&nbsp; DoCmd.OpenReport
"rptPojectOffshore", acViewPreview, stgFilter<BR>&nbsp;&nbsp;&nbsp; <BR>End
Sub<BR>&nbsp;<BR>When opening the report the filter seems not to work? Even the
first attempt to print the filter gives no (blanc) return.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>REPORT</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Private Sub Report_Open(Cancel As
Integer)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp; 'On Error Resume
Next<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; Debug.Print "Filter: ";
Me.filter</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>End Sub<BR>&nbsp;&nbsp;&nbsp; </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Who can I get my Filter working?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>I have never realy understood the diffrence between
using a filter or the WHERE clause.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><BR><FONT face=Arial size=2>Thx a lot,<BR>Ludovic</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0170_01C729F5.418FFBF0--
Click to expand...
 
Rick, thx for your advice. I never got the hang of the diffrence of the Filter and the Where clause, and still not completely...

Now after your advice I did put my arguments in the Where Clause as I used to do, but it does not appear in the Filter in the report (so that I can turn it on or off). Can the Filter only be from within the report and than turned on?

Thx,
Ludovic

Rick Brandt said:
I do try to open a report with a command button from a form, the
report should be filtered depending on the users choice on the form.

FORM

Private Sub cmdPrint_Click()
Dim stgFilter As String

'Filter not transfered ??????????
stgFilter = "([Type] = '" & Me.lstProject & "')"
DoCmd.OpenReport "rptPojectOffshore", acViewPreview, stgFilter

End Sub

When opening the report the filter seems not to work? Even the first
attempt to print the filter gives no (blanc) return.

REPORT

Private Sub Report_Open(Cancel As Integer)

'On Error Resume Next

Debug.Print "Filter: "; Me.filter

End Sub


Who can I get my Filter working?

I have never realy understood the diffrence between using a filter or
the WHERE clause.
Click to expand...

That lack of understanding is the issue. Move your stgFilter variable into the WHERE argument and out of the FILTER argument. The filter argument is meant to hold the name of a query to be used instead of the report's normal RecordSource.

In all my years of using Access I have never once used the Filter argument. Only the WHERE argument. It is a bit confusing because the WHERE argument actually "filters" the report and you will see it in the report's filter property. The same does not happen when using the FILTER argument.
 
Vsn said:
Rick, thx for your advice. I never got the hang of the diffrence of the Filter
and > the Where clause, and still not completely...

Now after your advice I did put my arguments in the Where Clause as I used to
it on or > off). Can the Filter only be from within the report and than turned
on?
Click to expand...

Not sure if you can do what (it appears) you want.

The Filter property as set by the WHERE argument of the OpenReport method is not
available yet in the Open Event of the Report, but is in later ones. I tested
the Page event and the Report Header Format events and the filter was available
there. However; it doesn't look like those later events can be used to turn the
filter on and off.

I must say that I have never had this requirement. Normally the code that is
opening the Report decides whether a filter should be applied, not code in the
Report itself.

I HAVE applied filtering in the Open event of a Report, but never in a
circumstance where I needed to also test whether the Filter property already had
been populated. I normally am testing some other condition like the value of a
control on an open Form or of a variable.
 
Rick,

My idea was to create a report that would function as stand alone as well as
being fired from a form, the text in the header of the report should depend
on the way (filter) it is opend. I now solved this by using the Argument.
Thx for the help provided.

Ludovic
 
Jeff, I have managed to do what I was looking for thx for your help.

Ludovic
Jeff C said:
A different approach would be to have a query that reflects the same
record source as your form only add criteria to an ID field in the
query so that it reflects the current record on the form ie:
forms!myform.txt_ID

Simply base your report on this new query and open with your command
button

Hi All,


I do try to open a report with a command button from a form, the report
should be filtered depending on the users choice on the form.

FORM

Private Sub cmdPrint_Click()
Dim stgFilter As String

'Filter not transfered ??????????
stgFilter = "([Type] = '" & Me.lstProject & "')"
DoCmd.OpenReport "rptPojectOffshore", acViewPreview, stgFilter

End Sub

When opening the report the filter seems not to work? Even the first
attempt to print the filter gives no (blanc) return.

REPORT

Private Sub Report_Open(Cancel As Integer)

'On Error Resume Next

Debug.Print "Filter: "; Me.filter

End Sub


Who can I get my Filter working?

I have never realy understood the diffrence between using a filter or the
WHERE clause.


Thx a lot,
Ludovic


------=_NextPart_000_0170_01C729F5.418FFBF0
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 2449

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.3020" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Arial size=2>Hi All,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><BR><FONT face=Arial size=2>I do try to open a report with a command
button
from a form, the report should be filtered depending on the users choice
on the
form.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>FORM</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Private Sub
cmdPrint_Click()<BR>&nbsp;&nbsp;&nbsp;
Dim stgFilter As String<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp;
'Filter not
transfered ??????????<BR>&nbsp;&nbsp;&nbsp; stgFilter = "([Type] = '"
&amp;
Me.lstProject &amp; "')"<BR>&nbsp;&nbsp;&nbsp; DoCmd.OpenReport
"rptPojectOffshore", acViewPreview, stgFilter<BR>&nbsp;&nbsp;&nbsp;
<BR>End
Sub<BR>&nbsp;<BR>When opening the report the filter seems not to work?
Even the
first attempt to print the filter gives no (blanc) return.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>REPORT</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Private Sub Report_Open(Cancel As
Integer)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp; 'On Error Resume
Next<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp;&nbsp; Debug.Print "Filter: ";
Me.filter</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>End Sub<BR>&nbsp;&nbsp;&nbsp; </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Who can I get my Filter
working?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>I have never realy understood the diffrence
between
using a filter or the WHERE clause.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><BR><FONT face=Arial size=2>Thx a lot,<BR>Ludovic</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0170_01C729F5.418FFBF0--
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

report record source 3
Set filter in subreport 7
Filter problem 0
Filter Report by Form 2
Filtering Reports RecordSource 4
Filter Sub Report 3
Excel Export Filtered Form Data To Excel 0
Filter a report based on combox value 2

Back
Top