Filtering Reports RecordSource

[Bill]

With this code (below), my intent is to pass the
filter to the report. I assumed that this form of
OpenReport would automatically cause the
reports FilterOn property to become TRUE
and that the filter expression would be
assigned to the reports Filter.

Debug code in the report's OnOpen code
reveals: Me.Filter = "" and
Me.FilterOn = False

The table field is either "Spring" or "Fall"
and is of the form: "aa-nnnn". So, I'm
looking to have the RecordSource filtered
to "Spring" or "Fall" to have a dash "-" as
the distinguishing characteristic.
===========(Begin Code)========
Private Sub cmdPrintInvite_Click()
Dim InviteParms As String
Dim strFilter As String

With Me
InviteParms = .RetDate & ";" & .PayTo & ";" & .[ID-SendTo] & ";" &
..PayAmt & ";"
InviteParms = InviteParms & .RecBy & ";" & .txtRegards
End With

strFilter = Season & " Like ""-"""

DoCmd.OpenReport "rptInvitation", acViewPreview, , strFilter, , InviteParms
DoCmd.RunCommand acCmdZoom100
End Sub
===========(End Code)========

 

[Allen Browne]

Bill there are several issues here.

1. If you pass a valid Filter string i the WhereCondition of OpenReport,
Access does set the Filter property of the report. However, there is a bug
in all versions of Access, so it does not reliably set the FilterOn
property. This is a real pain, since you cannot tell if the Filter is
actually applied, of if it is merely an artifact of a previous filter.

2. If you add the line:
Debug.Print strFilter
just before the OpenReport line, you will see that the filter string will
may something like this:
0 Like "-"
or possibly just:
Like "-"
We don't know what Season is - perhaps a field on your form, perhaps an
undeclared variable. In any case, that's a chance that it could be null, and
the string would be invalid.

3. In any case, the Like operator without any wildcards will match only if
field that contain exactly the dash (which is probably no records.) You
probably need to add * as the wildard.

4. You need to be aware that Access does not handle fields that contain a
dash reliably. The results are different, depending on whether the field is
indexed or not. More info:
http://support.microsoft.com/kb/271661/en-us

Hopefully that's enough to enable you to sort out the mix of your bugs and
Microsoft's.

 

[Bill]

I remembered the wildcards after I'd shut down and
had gone to bed for the night.

"Season", now "strSeason" is a string variable that was
passed into the form that subsequently issues the OpenReport.
It is set to a field name that is contained in the report's
RecordSource.

The WHERE clause included in the DoCmd.OpenReport
is either "[Spring] Like *-*" or "[Fall] Like *-*". Access
wouldn't accept either of those in the DoCmd.OpenReport.
I assume then, as you pointed out, that Access simply
couldn't deal with the dash reliably.

To resolve the issue, I appended "strSeason" to the
parameter list passed to the report. Thus, when the report's
OnOpen code finished parsing its OpenArgs, it set the
report's filter.

Thanks for your help and tips about Access's treatment of
the "dash".

Bill

Bill there are several issues here.

1. If you pass a valid Filter string i the WhereCondition of OpenReport,
Access does set the Filter property of the report. However, there is a bug
in all versions of Access, so it does not reliably set the FilterOn
property. This is a real pain, since you cannot tell if the Filter is
actually applied, of if it is merely an artifact of a previous filter.

2. If you add the line:
Debug.Print strFilter
just before the OpenReport line, you will see that the filter string will
may something like this:
0 Like "-"
or possibly just:
Like "-"
We don't know what Season is - perhaps a field on your form, perhaps an
undeclared variable. In any case, that's a chance that it could be null,
and the string would be invalid.

3. In any case, the Like operator without any wildcards will match only if
field that contain exactly the dash (which is probably no records.) You
probably need to add * as the wildard.

4. You need to be aware that Access does not handle fields that contain a
dash reliably. The results are different, depending on whether the field
is indexed or not. More info:
http://support.microsoft.com/kb/271661/en-us

Hopefully that's enough to enable you to sort out the mix of your bugs and
Microsoft's.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

With this code (below), my intent is to pass the
filter to the report. I assumed that this form of
OpenReport would automatically cause the
reports FilterOn property to become TRUE
and that the filter expression would be
assigned to the reports Filter.

Debug code in the report's OnOpen code
reveals: Me.Filter = "" and
Me.FilterOn = False

The table field is either "Spring" or "Fall"
and is of the form: "aa-nnnn". So, I'm
looking to have the RecordSource filtered
to "Spring" or "Fall" to have a dash "-" as
the distinguishing characteristic.
===========(Begin Code)========
Private Sub cmdPrintInvite_Click()
Dim InviteParms As String
Dim strFilter As String

With Me
InviteParms = .RetDate & ";" & .PayTo & ";" & .[ID-SendTo] & ";" &
.PayAmt & ";"
InviteParms = InviteParms & .RecBy & ";" & .txtRegards
End With

strFilter = Season & " Like ""-"""

DoCmd.OpenReport "rptInvitation", acViewPreview, , strFilter, ,
InviteParms
DoCmd.RunCommand acCmdZoom100
End Sub
===========(End Code)========

 

[Allen Browne]

Okay, all solved. That's good.

Just for reference, the filter string:
"[Spring] Like *-*"
probably needed to be:
"[Spring] Like ""*-*"""

If the extra quotes don't make sense to you, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

 

[Bill]

The actual code in the OnOpen event code reads:

Me.Filter = "[" & strTemp(6) & "] Like '*-*'"
Me.FilterOn = True

Where strTemp(6) is the current value of strSeason
as set in the invoking code and passed as the 7th
argument to the report.

I'm familiar with the double-quote usage when a
quoted string appears within a string. (SQL Value
syntax can get pretty messy looking when field
expressions are strings.)

Thanks again,
Bill

Okay, all solved. That's good.

Just for reference, the filter string:
"[Spring] Like *-*"
probably needed to be:
"[Spring] Like ""*-*"""

If the extra quotes don't make sense to you, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I remembered the wildcards after I'd shut down and
had gone to bed for the night.

"Season", now "strSeason" is a string variable that was
passed into the form that subsequently issues the OpenReport.
It is set to a field name that is contained in the report's
RecordSource.

The WHERE clause included in the DoCmd.OpenReport
is either "[Spring] Like *-*" or "[Fall] Like *-*". Access
wouldn't accept either of those in the DoCmd.OpenReport.
I assume then, as you pointed out, that Access simply
couldn't deal with the dash reliably.

To resolve the issue, I appended "strSeason" to the
parameter list passed to the report. Thus, when the report's
OnOpen code finished parsing its OpenArgs, it set the
report's filter.

Thanks for your help and tips about Access's treatment of
the "dash".

Bill