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

  • Thread starter Thread starter MGFoster
  • Start date Start date
M

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/AwUBQAiiEIechKqOuFEgEQJRvQCgtkHWY5T7baqdjzdwmzEZ7MRypb4AmwTS
LhnPZJOYsVceVBdgJhfrF+MR
=otbq
-----END PGP SIGNATURE-----
 
MG:

What you are running into is the difference in operation between a Jet based
database engine and the MSDE (SQL Server) based engine. When you change
database engines, differences in required syntax should be expected; this is
not a bug. There are lots of differences in the way you should construct
operations in an ADP versus a typical Jet based db.

Since you've found the proper syntax to make the report work with the MSDE
engine, then modifying your code should be a piece of cake to create a well
formed WHERE clause to use as a filter using the Or operator using a simple
loop.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Steve,

If I were to create multiple OR statements for each ID returned by the
ListBox I might run into a limit on the length of a Filter statement.
IOW, If the user selects 50 IDs from the ListBox and I OR all of them
the length of the Filter statement might be > 500 chars. If the
Filter statement will only accept 256 chars the filter may fail or
return incorrect data. The IN( ) clause keeps the Filter statement
much shorter. Do you know the max length allowed a Filter statement?

And, anyway.... According to the documentation the Filter property
should accept any valid SQL WHERE clause (without the WHERE). The IN(
) statement is a valid SQL statement in JET & SQL Server (and MSDE).
So, why doesn't the .adp Report Filter work as advertised? If a
feature doesn't work as advertised I consider that a bug.

Regards,

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

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

iQA/AwUBQBArrIechKqOuFEgEQKDUgCcC3odmHi/XxPyAnhRHNohlFe7sLIAoMhJ
q5KYCysSlKi0PD/80fsV3I6S
=1c9P
-----END PGP SIGNATURE-----
 
Back
Top