Access 2000 SR-1 ApplyFilter bug

[vacant]

I recently discovered a handful of machines on the network were running 2000 SR-1, and as such my database was not functioning correctly because of a bug with applying filters in that release, the details of which are given here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269380

In a nutshell, the bug means the filter works correctly the first time it is applied, but after that it always returns the same results. The workaround suggested on the above link involves emptying the filter before applying a new one.
My form gives users the option of filtering the records by 5 criteria, and although the above method does get around the problem for 4 of these, it fails to work on the 5th.
The only difference I can see is that for the 4 that work the criteria is straightforward, e.g. i have a text box that the user enters a post code in called txtPCode

Sub exactPCode_Click() ` filter for only this post code
call Sub1: (set filter to “” then turn filter off) `workaround pt1
Me.Filter = “PostCode = Forms!frmmain!txtPCode” ` set filter criteria
call Sub2: (turn filter on) `workaround pt2
End Sub

and this works fine

However, the one that doesn’t work involves more than one criteria

Sub expandPCode_Click() `filter for post code plus or minus 1
(call Sub1)
`set filter criteria
Me.Filter = “PostCode = Forms!frmmain!txtPCode Or PostCode = Left(Forms!frmmain!txtPCode, 2) & Right(Forms!frmmain!txtPCode, 2) + 1 Or PostCode = Left(Forms!frmmain!txtPCode, 2) & Right(Forms!frmmain!txtPCode, 2) – 1”
(call Sub2)
End Sub

This code works fine in 2000 but in 2000 SR-1 the bug crops up again, i.e. it works the first time of asking, but then just returns the same records each time after
I’m at a loss…

 

[JeremyNYC]

Is SR-1 the latest patch?

jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[vacant]

Well hello again Jeremy.
I see there is a Service Pack 2, but though this claims to fix a whole lot of other issues, this particular issue with applying filters is not listed.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q276257&

I'll have a word with the ICT guys and get them to try it on one anyway. Boy am I going to be popular, they only have one version now (SR-1), and they've been installing that all over the county.

 

[JeremyNYC]

Well, you may be unpopular with a few locals, but the fact is, any decent organization will see your action as something worthy of a reward--you are bring a problem to their attention. If it's an organization big enough to be &quot;all over the country&quot; they'll have the money to fix it. Of course, if they're that big, it may take six months for the idea to be considered, six months for a plan to be drawn, and six months to implement it. By then we'll have Access YQ, and it will have a whole new set of bugs. <G>

Speaking of bugs, while you'e at it, tell the IT folks that Access XP is much less buggy than 2K. Much.

Thanks for the info, though. I'm actually in the process of lobbying some corporate IT types to let me use XP instead of 2K, and this little bit will help.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.