ACC2K2: Dynamic report filters failing on IN operator

[MGFoster]

-----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_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
Me.FilterOn = True
End If

The "frm.FacilitiesID" 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAiiEIechKqOuFEgEQJRvQCgtkHWY5T7baqdjzdwmzEZ7M Rypb4AmwTS
LhnPZJOYsVceVBdgJhfrF+MR
=otbq
-----END PGP SIGNATURE-----

[Salad]

MGFoster wrote:

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_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
Me.FilterOn = True
End If

The "frm.FacilitiesID" 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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....forget those
things....instead of the following code in your OnOpen event
DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
If Not IsLoaded(FORM_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
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.FacilitiesIDs & ")"
would be incorrect.....somewhere.

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]

Salad wrote:

MGFoster wrote:

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_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
Me.FilterOn = True
End If

The "frm.FacilitiesID" 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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....forget those
things....instead of the following code in your OnOpen event
DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
If Not IsLoaded(FORM_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
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.FacilitiesIDs & ")"
would be incorrect.....somewhere.

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?

-----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.FacilitiesIDs & ")"

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 "FacilitiesIDs" gets the selected IDs from
the List Box & puts them in a comma-delimited string.

Regards,

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

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

iQA/AwUBQAo1qYechKqOuFEgEQIlywCgiBoNRXBZLZr1djO+1VfflS V1pLYAnjmm
1MdB4JGc7dXBSAiePIAIQTnR
=btD2
-----END PGP SIGNATURE-----

[Salad]

MGFoster wrote:

Salad wrote:

MGFoster wrote:

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_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
Me.FilterOn = True
End If

The "frm.FacilitiesID" 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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....forget those
things....instead of the following code in your OnOpen event
DoCmd.OpenForm FORM_CRIT, , , , , acDialog, Me.Name
If Not IsLoaded(FORM_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
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.FacilitiesIDs & ")"
would be incorrect.....somewhere.

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?

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 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>.
As for

Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"

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."

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...if 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(YourStringFromListBos,"*" & [ID] & "*") > 0 and check the criteria for
True.

Good luck

[MGFoster]

-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQAwjvIechKqOuFEgEQIOhQCgun4splb0sNCvjwuYxe7ReR/PNpQAoLlg
nYUTH4y+wMFxUBJGyiTJMI6A
=FAu6
-----END PGP SIGNATURE-----

< snip previous posts >

OK, here's some more suggestions...if 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(YourStringFromListBos,"*" & [ID] & "*") > 0 and check the criteria for
True.

Good luck

[MGFoster]

-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQA2LuYechKqOuFEgEQIPnQCgkZ5Xi2+FAz6NVbTMwjDvZD JMqmMAnjJD
EX/v9K4kP069lHbHgKa3k2Cu
=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_CRIT) Then
Cancel = True
Else
Dim frm As Form_frmDeprSumCriteria_rpt
Set frm = Forms(FORM_CRIT)
Me.Filter = "FacilityID In (" & frm.FacilitiesIDs & ")"
Me.FilterOn = True
End If

The "frm.FacilitiesID" 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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQAiiEIechKqOuFEgEQJRvQCgtkHWY5T7baqdjzdwmzEZ7M Rypb4AmwTS
LhnPZJOYsVceVBdgJhfrF+MR
=otbq
-----END PGP SIGNATURE-----