ADO filter problem with spaces

[LPurvis]

Chr returns the string character based upon the passed ASCII character code.
ASC does the reverse. Hex returns the Hex value of a passed Long.
The Hex representation of a character is therefore
Hex(Asc(strChar))

The thing to bear in mind about ADO (compared to the more traditional/predictable data access with DAO) is that it is entirely dependent upon the provider for its functionality (and yes - even its behaviour).
The example page you've found there is using the Active Directory Provider and as such it obeys its own set of standards - even when in SQL mode (which that page isn't dealing with).

ADO with the Jet and SQL Server providers does allow the escaping of special characters (I believe I mentioned it earlier?). It's entirely possible by creating a character class [].
e.g.
WHERE FieldName Like '%[%]%'
returns rows with an actual % symbol in.

Obviously that doesn't help with your quotes though.
The quotes here are delimiters. They signal the end of the string. AFAIK nothing, other than doubling up, will escape that meaning.

However - I still don't see why this is happening in the first place.
Testing myself the suggested code works against a Jet, Linked SQL table, linked SQL view and direct ADO connection to SQL Server.
I can't think offhand what, in your code, might be causing the issue.
Can you show your full code and table anyway?
The only thing I notice in your code is that you're requesting a Keyset cursor type - but will be getting a Static due to your cursor location. (But then that's just from some sloppy/rushed example code knocked together for the original example in the first place... whoever put that together ;-)
It shouldn't be affecting anything at all though.

All that said - the change you've made is actually in line with my actual recommendation in my first post here. So that's good.
I too just hate to leave something unsolved.

Cheers.

 

[georgedwilkinson]

I don't mind posting it (the data is not particularly sensitive but very huge), but after working with it in my development system, I highly simplified it by just using your sample (can download at http://www.databasedevelopment.co.uk/experts/ComboFilter.zip) and changing your name to Leigh O'Purvis (sorry). So I have two modified rows in the employee table: O'Purvis, Leigh and Leigh O'Purvis (with apostrophes and spaces). I made this decision because I thought my data might be broken (reminds me of Oracle Designer support--lol).

After making those data changes:

In the bottom combo box, just typing "leig" causes the original error on the line:

rstBig.Filter = "Employee Like '" & Me.cboRst2.Text & "%'"

Changing that line to:

rstBig.Filter = "Employee Like '" & Replace(Me.cboRst2.Text, "'", "''") & "%'"

and typing in "leigh o'p" on the bottom combo-box displays the other problem (no error but no worky). Or typing in "o'p" will give the other error. Using any of the many fine suggestions here yield similar results.

Typing all of this in is required in my production system since the "site" names are based on the company's name for that location at the time in question. So, my earlier example of "widow" was actually part of the name of a power plant in Alabama with multiple reactors. Example: Widow's Creek Power Plant, Widow's Creek Reactor 5, Widow's Creek Unit 3, etc. ad-inifinitum. This is a relatively simple example.

I have tried this in 2002 (Windows XP) and 2007 (Vista). The results are similar.

I'm going forward with a version of your code which doesn't use .Filter on the result set (thank you!). It is sufficient for my needs, for now.

I really thank everyone for their help. I'm still curious about the resolution, if there is one. I'd love to know if anyone gets a different result (the test is really simple to perform starting with Leigh's base code).

 

[LPurvis]

Hi George.

OK - had a bit of a look at this.
I see what you mean with the problem you're seeing.
I wasn't actually testing that.
You see - the reason I could see that the filtering was working - is that it is!
The recordset is filtering exactly as it should.
If we test the recordset immediately prior to assignment to the list control - the recordcount is accurate (filtered down to the appropriate level). Once you assign the recordset to the control - the list is blank (it's ListCount is zero) - but the control's recordset property is perfectly well aware of those two records - it is still showing an accurate count.

This is a bug in Access implementation of filtered ADO recordsets.
(I'll confirm, example and report it to MS when I get the chance).

The recordset doubled up filter quotes isn't accepted by the control as a genuine recordset. I've not investigated why as yet.
However - I have a workaround for you (of sorts).
Bearing in mind that the aim of this game (rightly or wrongly) is to prevent the need to subsequent server data requests.
Adding a relatively small amount of client overhead is still within the rules.
The workaround for now is to just create a new recordset and fill it with the filtered resultset.
This works (and is very fast) or relatively low recordcounts.
IMO you wouldn't want to be presenting a long list anyway - you should up your minimum character count to prevent that.
(Indeed - I've already voiced concerns about the loading, and persisting, of a very large recordset).

Anyway - have a look at this ammended example.
The workaround has been implemented only in the combo example - and only on the second combo method. (You'll see it and how to simply add it elsewhere using the fCopyADORecordset function).

Cheers.

 

[georgedwilkinson]

(I'll confirm, example and report it to MS when I get the chance).

I appreciate that.

However - I have a workaround for you (of sorts).
...snip...
Anyway - have a look at this ammended example.
The workaround has been implemented only in the combo example - and only on the second combo method. (You'll see it and how to simply add it elsewhere using the fCopyADORecordset function).

You are a gem among gems. A giant among giants! A...well, you get the point.

I'll check it out. I already implemented something I'm not happy with but I gotta resolve this for my own personal reasons.

 

[LPurvis]

No worries George.
Let me know how you get on with it - and I'll probably end up changing the online demo version to something more like the above (just rounded out to all examples).

Cheers