ACC2K2: Dynamic report filters failing on IN operator

**Salad** · Nov 12 '05, 07:12 PM

Re: ACC2K2: Dynamic report filters failing on IN operator

MGFoster wrote:
[color=blue]
> I've converted an ACC97 .mdb file to an ACC2K2 .adp. A report that
> worked in ACC97 doesn't work in ACC2K2.
>
> Report setup:
>
> ACC97 ACC2K2 (SP-2)
> -------------- ---------------------------
> RecordSource Query Stored procedure
> 2 Params from 2 Input Parameters from
> form references form references
>
> Filter Set up in Set up in Report OnOpen
> Report OnOpen
>
> Database Jet 3.5 MSDE (local)
>
> OnOpen VBA for both versions:
>
> DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
> If Not IsLoaded(FORM_C RIT) Then
> Cancel = True
> Else
> Dim frm As Form_frmDeprSum Criteria_rpt
> Set frm = Forms(FORM_CRIT )
> Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
> Me.FilterOn = True
> End If
>
> The "frm.Facilities ID" is a GET property on the criteria form that
> returns a comma-delimited string of numeric IDs. If I change the
> filter to:
>
> FacilityID = 9 OR FacilityID = 1 OR FacilityID = 8
>
> the report works OK. The report fails if the filter looks like this:
>
> FacilityID In (9, 1, 8)
>
> I get 2 consecutive error pop-ups of this message:
>
> "One or more operators in the filter expression is invalid.
> For a valid list of operators refer to the help file."
>
> The MS KB article 264254 only refers to this error message/problem in
> ACC2K, JET 4.0: Form filters. The article recommended I install JET
> 4.0's SP-8. I did & the error persists.
>
> Any thoughts, recommendations ?
>
> Thanks,
>
> MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
> Oakland, CA (USA)[/color]

I haven't worked with ADPs and I started getting hungup on your use of
the word "report" when you are dealing with forms and what FORM_CRIT is
(probably a constant) but I finally thoiught....for get those
things....inste ad of the following code in your OnOpen event
DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
If Not IsLoaded(FORM_C RIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSum Criteria_rpt
Set frm = Forms(FORM_CRIT )
Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
Me.FilterOn = True
End If
when you are testing and debugging what happens if you simply state
Me.Filter = "FacilityID In (9,1,8)"
Me.FilterOn = True

Your syntax appears correct. But if simply setting the filter explicity
in the OnOpen and it works but fails with your current method, then the
line
Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
would be incorrect.....s omewhere.

Or even try
Me.Filter = "FacilityID In (9)"
Me.FilterOn = True
for a filter on a single code....just to ensure that the IN
operator/predicate is the problem...or have you already done this?

**MGFoster** · Nov 12 '05, 07:13 PM

Re: ACC2K2: Dynamic report filters failing on IN operator

Salad wrote:[color=blue]
> MGFoster wrote:
>
>[color=green]
>>I've converted an ACC97 .mdb file to an ACC2K2 .adp. A report that
>>worked in ACC97 doesn't work in ACC2K2.
>>
>>Report setup:
>>
>> ACC97 ACC2K2 (SP-2)
>> -------------- ---------------------------
>>RecordSourc e Query Stored procedure
>> 2 Params from 2 Input Parameters from
>> form references form references
>>
>>Filter Set up in Set up in Report OnOpen
>> Report OnOpen
>>
>>Database Jet 3.5 MSDE (local)
>>
>>OnOpen VBA for both versions:
>>
>> DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
>> If Not IsLoaded(FORM_C RIT) Then
>> Cancel = True
>> Else
>> Dim frm As Form_frmDeprSum Criteria_rpt
>> Set frm = Forms(FORM_CRIT )
>> Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
>> Me.FilterOn = True
>> End If
>>
>>The "frm.Facilities ID" is a GET property on the criteria form that
>>returns a comma-delimited string of numeric IDs. If I change the
>>filter to:
>>
>> FacilityID = 9 OR FacilityID = 1 OR FacilityID = 8
>>
>>the report works OK. The report fails if the filter looks like this:
>>
>> FacilityID In (9, 1, 8)
>>
>>I get 2 consecutive error pop-ups of this message:
>>
>> "One or more operators in the filter expression is invalid.
>> For a valid list of operators refer to the help file."
>>
>>The MS KB article 264254 only refers to this error message/problem in
>>ACC2K, JET 4.0: Form filters. The article recommended I install JET
>>4.0's SP-8. I did & the error persists.
>>
>>Any thoughts, recommendations ?
>>
>>Thanks,
>>
>>MGFoster:::mg f00 <at> earthlink <decimal-point> net
>>Oakland, CA (USA)[/color]
>
>
> I haven't worked with ADPs and I started getting hungup on your use of
> the word "report" when you are dealing with forms and what FORM_CRIT is
> (probably a constant) but I finally thoiught....for get those
> things....inste ad of the following code in your OnOpen event
> DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
> If Not IsLoaded(FORM_C RIT) Then
> Cancel = True
> Else
> Dim frm As Form_frmDeprSum Criteria_rpt
> Set frm = Forms(FORM_CRIT )
> Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
> Me.FilterOn = True
> End If
> when you are testing and debugging what happens if you simply state
> Me.Filter = "FacilityID In (9,1,8)"
> Me.FilterOn = True
>
> Your syntax appears correct. But if simply setting the filter explicity
> in the OnOpen and it works but fails with your current method, then the
> line
> Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
> would be incorrect.....s omewhere.
>
> Or even try
> Me.Filter = "FacilityID In (9)"
> Me.FilterOn = True
> for a filter on a single code....just to ensure that the IN
> operator/predicate is the problem...or have you already done this?[/color]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I have tried all those Filters - opened the report in design view and
in the Filter property put FacilityID In (9) and Filter On Yes and got
the same error(s). As for

Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"

being incorrect - it is correct because it worked in earlier versions
(.mdb files) of Access. And, as advertised by Access Help articles,
(paraphrase) "any SQL statement that will work in an SQL WHERE clause
will work in the Filter property."

FORM_CRIT is the string constant for the name of the criteria form the
report opens so the user can select the required Facility IDs from a
List Box. The Get Property "Facilities IDs" gets the selected IDs from
the List Box & puts them in a comma-delimited string.

Regards,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAo1qYechKq OuFEgEQIlywCgiB oNRXBZLZr1djO+1 VfflSV1pLYAnjmm
1MdB4JGc7dXBSAi ePIAIQTnR
=btD2
-----END PGP SIGNATURE-----

**Salad** · Nov 12 '05, 07:13 PM

APD Gurus...Can you help out? Re: ACC2K2: Dynamic report filtersfailing on IN operator

MGFoster wrote:
[color=blue]
> Salad wrote:[color=green]
> > MGFoster wrote:
> >
> >[color=darkred]
> >>I've converted an ACC97 .mdb file to an ACC2K2 .adp. A report that
> >>worked in ACC97 doesn't work in ACC2K2.
> >>
> >>Report setup:
> >>
> >> ACC97 ACC2K2 (SP-2)
> >> -------------- ---------------------------
> >>RecordSourc e Query Stored procedure
> >> 2 Params from 2 Input Parameters from
> >> form references form references
> >>
> >>Filter Set up in Set up in Report OnOpen
> >> Report OnOpen
> >>
> >>Database Jet 3.5 MSDE (local)
> >>
> >>OnOpen VBA for both versions:
> >>
> >> DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
> >> If Not IsLoaded(FORM_C RIT) Then
> >> Cancel = True
> >> Else
> >> Dim frm As Form_frmDeprSum Criteria_rpt
> >> Set frm = Forms(FORM_CRIT )
> >> Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
> >> Me.FilterOn = True
> >> End If
> >>
> >>The "frm.Facilities ID" is a GET property on the criteria form that
> >>returns a comma-delimited string of numeric IDs. If I change the
> >>filter to:
> >>
> >> FacilityID = 9 OR FacilityID = 1 OR FacilityID = 8
> >>
> >>the report works OK. The report fails if the filter looks like this:
> >>
> >> FacilityID In (9, 1, 8)
> >>
> >>I get 2 consecutive error pop-ups of this message:
> >>
> >> "One or more operators in the filter expression is invalid.
> >> For a valid list of operators refer to the help file."
> >>
> >>The MS KB article 264254 only refers to this error message/problem in
> >>ACC2K, JET 4.0: Form filters. The article recommended I install JET
> >>4.0's SP-8. I did & the error persists.
> >>
> >>Any thoughts, recommendations ?
> >>
> >>Thanks,
> >>
> >>MGFoster:::mg f00 <at> earthlink <decimal-point> net
> >>Oakland, CA (USA)[/color]
> >
> >
> > I haven't worked with ADPs and I started getting hungup on your use of
> > the word "report" when you are dealing with forms and what FORM_CRIT is
> > (probably a constant) but I finally thoiught....for get those
> > things....inste ad of the following code in your OnOpen event
> > DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
> > If Not IsLoaded(FORM_C RIT) Then
> > Cancel = True
> > Else
> > Dim frm As Form_frmDeprSum Criteria_rpt
> > Set frm = Forms(FORM_CRIT )
> > Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
> > Me.FilterOn = True
> > End If
> > when you are testing and debugging what happens if you simply state
> > Me.Filter = "FacilityID In (9,1,8)"
> > Me.FilterOn = True
> >
> > Your syntax appears correct. But if simply setting the filter explicity
> > in the OnOpen and it works but fails with your current method, then the
> > line
> > Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
> > would be incorrect.....s omewhere.
> >
> > Or even try
> > Me.Filter = "FacilityID In (9)"
> > Me.FilterOn = True
> > for a filter on a single code....just to ensure that the IN
> > operator/predicate is the problem...or have you already done this?[/color]
>
> I have tried all those Filters - opened the report in design view and
> in the Filter property put FacilityID In (9) and Filter On Yes and got
> the same error(s).[/color]

As you are one of the gurus in this newsgroup that have helped people out over
the years, I figured as much...I asked in order to veriffy the obvious was
checked out <g>.
[color=blue]
> As for
>
> Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
>
> being incorrect - it is correct because it worked in earlier versions
> (.mdb files) of Access. And, as advertised by Access Help articles,
> (paraphrase) "any SQL statement that will work in an SQL WHERE clause
> will work in the Filter property."[/color]

That's what surprised me. The code looks fine. And it works like you state
in a MDB. The only difference that I see is that you use MSDE instead of Jet
and this is an ADP instead of a MDB. I am hoping the header change I made
will get some folks familiar with ADPs to take a look at this problem.

The only other thing I can suggest is to create a query that uses the IN
operator/predicate and futz with it in the query builder instead of a form or
report and if you can get the query to work, use that SQL instead....but again
I state the obvious.

OK, here's some more suggestions...i f you can't get the query to work and
folks familiar with ADPs can't help out is to bite the bullet, recognize that
what is advertised in Access Help may really be a teaser, and use the OR
clause and every now and then ask the question again and see if someone has a
solution.
Or....
.... perhaps take another tact and change IN to InStr(). You would have to
change the string or comparison...ex if your ID was 1 and the string was 11,
the number 1 would match. Maybe, just maybe, something like Instr() would
work. Ex:
Create a new column called AsteriskID and make the value something like
Instr(YourStrin gFromListBos,"* " & [ID] & "*") > 0 and check the criteria for
True.

Good luck

**MGFoster** · Nov 12 '05, 07:15 PM

Re: APD Gurus...Can you help out? Re: ACC2K2: Dynamic report filtersfailing on IN operator

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If I don't get any answers from any others on this ng I'll probably
use the Split() function to break the comma-delimited string into an
array & then create a series of OR functions. E.g.:

.... FacilitiyID = 1 OR FacilityID = 9 OR FacilityID = 8 ... etc. ...

In some RDBMS the query optimizer DOES translate the IN (...)
statement into a series of ORs. I believe Access report filters use
JET to filter the RecordSource data. Not sure. Therefore, JET would
be the db engine that handles the IN() clause, not MSDE. Perhaps an
Access expert who has more info on the Report/JET interface can answer
that - and, is this a JET problem or an MSDE problem?

- --
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAwjvIechKq OuFEgEQIOhQCgun 4splb0sNCvjwuYx e7ReR/PNpQAoLlg
nYUTH4y+wMFxUBJ GyiTJMI6A
=FAu6
-----END PGP SIGNATURE-----

< snip previous posts >
[color=blue]
> OK, here's some more suggestions...i f you can't get the query to work and
> folks familiar with ADPs can't help out is to bite the bullet, recognize that
> what is advertised in Access Help may really be a teaser, and use the OR
> clause and every now and then ask the question again and see if someone has a
> solution.
> Or....
> ... perhaps take another tact and change IN to InStr(). You would have to
> change the string or comparison...ex if your ID was 1 and the string was 11,
> the number 1 would match. Maybe, just maybe, something like Instr() would
> work. Ex:
> Create a new column called AsteriskID and make the value something like
> Instr(YourStrin gFromListBos,"* " & [ID] & "*") > 0 and check the criteria for
> True.
>
> Good luck
>
>[/color]

**MGFoster** · Nov 12 '05, 07:17 PM

Re: ACC2K2: Dynamic report filters failing on IN( ) operator --BUG??

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

New facts:

Opened the Stored Procedure (SP) that feeds the report and on the
Properties dialog box under the Data tab, put in the Filter:

FacilityID In (1, 8, 9)

Ran the SP and ALL FacilityID records were returned (should have only
returned FacilityIDs of 1, 8 & 9).

I changed the Filter to:

FacilityID = 1 OR FacilityID = 8 OR FacilityID = 9

and, again ALL FacilityID records were returned.

Is this a bug in SP filters?

Rgds,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQA2LuYechKq OuFEgEQIPnQCgkZ 5Xi2+FAz6NVbTMw jDvZDJMqmMAnjJD
EX/v9K4kP069lHbHgK a3k2Cu
=cMXn
-----END PGP SIGNATURE-----

MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've converted an ACC97 .mdb file to an ACC2K2 .adp. A report that
worked in ACC97 doesn't work in ACC2K2.

Report setup:

ACC97 ACC2K2 (SP-2)
-------------- ---------------------------
RecordSource Query Stored procedure
2 Params from 2 Input Parameters from
form references form references

Filter Set up in Set up in Report OnOpen
Report OnOpen

Database Jet 3.5 MSDE (local)

OnOpen VBA for both versions:

DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
If Not IsLoaded(FORM_C RIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSum Criteria_rpt
Set frm = Forms(FORM_CRIT )
Me.Filter = "FacilityID In (" & frm.FacilitiesI Ds & ")"
Me.FilterOn = True
End If

The "frm.Facilities ID" is a GET property on the criteria form that
returns a comma-delimited string of numeric IDs. If I change the
filter to:

FacilityID = 9 OR FacilityID = 1 OR FacilityID = 8

the report works OK. The report fails if the filter looks like this:

FacilityID In (9, 1, 8)

I get 2 consecutive error pop-ups of this message:

"One or more operators in the filter expression is invalid.
For a valid list of operators refer to the help file."

The MS KB article 264254 only refers to this error message/problem in
ACC2K, JET 4.0: Form filters. The article recommended I install JET
4.0's SP-8. I did & the error persists.

Any thoughts, recommendations ?

Thanks,

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAiiEIechKq OuFEgEQJRvQCgtk HWY5T7baqdjzdwm zEZ7MRypb4AmwTS
LhnPZJOYsVceVBd gJhfrF+MR
=otbq
-----END PGP SIGNATURE-----

ACC2K2: Dynamic report filters failing on IN operator

ACC2K2: Dynamic report filters failing on IN operator

Comment

Comment

Comment

Comment

Comment