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)
> >> -------------- ---------------------------
> >>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)[/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....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?[/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.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."[/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...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
