ADO filter problem with spaces (1 Viewer)

[georgedwilkinson]

I am trying to filter an ADO recordset and I keep getting an error when I use a filter criteria with a space (" ") in it. The indicated line below errors out with:

3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

The immediate window shows:

LongSiteName like "widow's %"

Before the failure, the filter had successfully been set to everything before the space (i.e. "w", "wi", "wid", "wido", "widow", "widow'", and "widow's"). Additionally, any time I put a space in the control, I get this error. There are matching records in the result set (i.e. "widow's creek").

FYI, the purpose of the code is to filter a combo box with an incredibly large (and growing) result set.

This code was originally from http://www.databasedevelopment.co.uk/examples.htm, but has been highly modified to fit my needs and programming standards. It works perfectly except the space problem in the filter criteria.

            If g_rstSiteList Is Nothing Then
                Set g_rstSiteList = New ADODB.Recordset
                g_rstSiteList.CursorLocation = adUseClient
                l_strSQL = "SELECT qryAESDDL.SiteID, " _
                    & "qryAESDDL.LongSiteName, qryAESDDL.ShortSiteName, " _
                    & "qryAESDDL.SiteCode, qryAESDDL.City, qryAESDDL.State, " _
                    & "qryAESDDL.Country " _
                    & "FROM qryAESDDL ORDER BY LongSiteName;"
                'Debug.Print "SQL: " & vbCrLf & l_strSQL
                g_rstSiteList.Open l_strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
                
            End If
            
            'Set the local filter for the recordset.
            l_strFilter = "LongSiteName like " & l_strDoubleQuote & MyText & "%" & l_strDoubleQuote
            Debug.Print l_strFilter
            [COLOR=red][B]g_rstSiteList.Filter = l_strFilter[/B][/COLOR]

Additional information--the BE is on SQL Server 2000, the FE is Access 2002, the query takes data from a linked SQL Server view.

I'm pretty much at my wits end on this and am probably no longer thinking straight. Any help is appreciated.

 

[Guus2005]

Use more quotes

Seems to me that the text behind the space is interpreted as a extra argument. Put some extra " signs around the criterium, add three double quotes instead of just one.

Share and Enjoy!

 

[georgedwilkinson]

I still get the same error. Only when I type a space in the control.

BTW, the l_strDoubleQuote is set to Chr(34) = ". I had to use that so criteria with apostrophes would work.

Any other recommendations?

 

[georgedwilkinson]

Deeper investigation:
Looks like this is Leigh Purvis' code. I tried his original code with the local database and it has no problems with spaces.

That makes me think the problem may be related to creating an ADO recordset against a local query based on a linked SQL Server view. I'm too tired right now to even imagine what that means I'm gonna have to do.

Any recommendations on how to proceed?

 

[Guus2005]

I always do this

l_strFilter = "LongSiteName like '" & MyText & "%'"

Please notice the single quotes after the Like and directly after the %

Share and Enjoy!

 

[georgedwilkinson]

I always do this

l_strFilter = "LongSiteName like '" & MyText & "%'"

Please notice the single quotes after the Like and directly after the %

Share and Enjoy!

That was my original code. It fails when I have a record with an apostrophe. It also doesn't remove the problem I have with the space throwing an error (on non-apostrophe records).

Additional information:
I loaded the entire table/view from SQL Server into a local Access table and re-wrote my query to use that. It still gives the error on the space, although Leigh's code does not. The only thing I'm doing different is going through a query to get to the data. I guess my next step is to go directly to the local Access table, which means incorporating all of the logic in my view in the ADO recordset.

Still accepting help/recommendations!

 

[Guus2005]

I always do this

l_strFilter = "LongSiteName like '" & MyText & "%'"

Please notice the single quotes after the Like and directly after the %

Share and Enjoy!

In case of apostrofes(?) i use the following code:

l_strFilter = "LongSiteName like """ & MyText & "%"""

HTH

 

[georgedwilkinson]

OK, I've been trying Leigh's code and my code and found that Leigh's code doesn't deal with apostrophes. When I add any combination of anything you (Guus) have suggested and anything I already knew to try to make apostrophes work, I get the error on Leigh's code too.

So, seems I can't filter for either apostrophes or spaces. Since there are more spaces (> 200K) in my data set than apostrophes (523), I guess I'm gonna have to get rid of the code that handles apostrophes (for now) and go back to a plain Jane:

l_strFilter = "LongSiteName like '" & MyText & "%'"

This really annoys me that I can't solve this. I really believe you should be able to write code that handles both spaces and apostrophes.

I'm still taking suggestions.

 

[Guus2005]

Please hold on for the Wow effect...

 

[Guus2005]

Miracles will take slightly longer

I used * instead of the %. See the attachment

Share and enjoy!

 

[georgedwilkinson]

Hmmm. I had already tried that within the filter criteria...it didn't work.

That doesn't filter the data in local ADO/Access memory. The objective is to speed up a combo-box lookup response time, enable re-use of the same recordset over multiple combo-boxes in the application, and overcome Access 2002's limit of 64K items in a DDL (http://support.microsoft.com/kb/187342).

Since I added 80K records to the list last week, the system has slowed down to a crawl and users cannot see sites that start with "S" and up. I need something that will meet the objectives and still work with the data.

As I said earlier, the base code for this was provided at http://www.databasedevelopment.co.uk/examples.htm, which I'm guessing is Leigh Purvis' site (at least, it seems to be Leigh's sample data), and the concept is expanded on by Allen Browne at http://allenbrowne.com/ser-32.html.

It occurred to me that if I could just supply a bound parameter to the filter, this would work perfectly. But I can't figure out how to do that and my eyes are red from no sleep.

Anyone have any idea how to solve this? Leigh? Paul, Bob, Allan? Anyone?

 

[Banana]

What if you parsed the string locally, and if there were space, pass it off to the query as this:

If InStr(MyString, " ") Then
    MyString = replace(Mystring, " ", Chr(32)) 'A space
End If

Not sure if this will help since it's still the same thing, but maybe the different formatting will help a bit.

Can you also verify if SQL server can parse the query with both space and single quotation mark? If it can, then verify the driver's parsing.

 

[LPurvis]

Well, as always (DAO, ADO, Jet or SQL Server) - if a criteria (for a filter or Where clause) is delimited by the same character it contains then you need to escape its meaning by doubling it up.
i.e.
l_strFilter = "LongSiteName like '" & Replace(MyText, "'", "''") & "%'"

That should be enough.


I have to say though - 80,000 rows? Obviously far too much to display in a list (and not just because it exceeds the ~65K Access limit) - which is why you're doing this.
But that is a lot of data to hold permanently in memory too.

Have you timed to see how quickly you can pull that down into a local MDB's table (via a passthrough perhaps)?
You know
DELETE * FROM tblLinkedLocalList
INSERT INTO tblLinkedLocalList SELECT * FROM qryPassThroughList

If it's going to add too long to your startup time - then you should probably fall back on just doing a fetch each time you need a partial list.
Yes - it's multiple requests to the server (for for each time the list needs to be requeried) but it's a light hit each time - and you're not gobbling up a huge amount of you client PC's RAM in one fell swoop.

If you implement the request via a Stored Proc and assign the ADO recordset you open against that proc to the list control (or just ammed the SQL of a passthrough and see how that goes and requery the list).

The idea of a local list which is filtered is kind on the server is all well and good.
But with everything there reaches a point when it's time to lean on the server a bit and protect local resources. ;-)

See how you go anyway.

 

[LPurvis]

Just to point out - once the data was in a local table, you wouldn't then be loading the recordset
Local data - fast to just query ah hoc. No bandwidth hogging large server grab.
Save the network - save the world! ;-)

 

[georgedwilkinson]

Update:
I decided to go with an ADO recordset that is rebuilt each time the left character(s) of the "search string" are changed. This is acceptable for now.

Thanks for all the suggestions.

I'd still like to know why you can't correctly ".Filter" an ADO recordset that has both apostrophes and spaces.

 

[LPurvis]

Odd that you'd need to.
Did you try what I listed - using the Replace as shown? (Which differed from the earlier suggestions made in this thread).

lol - just noticed in the question.
"highly modified to fit my needs and programming standards".
Note to the casual reader: A "standard" is the set of preferences and conventions one employs when coding.
Alternative explanation - example code I provide to the masses sucks. lol

 

[georgedwilkinson]

Odd that you'd need to.
Did you try what I listed - using the Replace as shown? (Which differed from the earlier suggestions made in this thread).

Yep. One of the first things I tried. Interestingly enough, that doesn't error out, it just doesn't work.

Using:

            l_strX = "SiteName Like '" & Replace(Trim(.Text), "'", "''") & "%'"
            Debug.Print l_strX
            MyRst.Filter = l_strX

Immediate window:
SiteName Like 'wido%' --Works OK
SiteName Like 'widow%' --Works OK
SiteName Like 'widow''%' --It stops "filtering" here, but no VBA error.

Alternate:

            l_strX = "SiteName Like """ & Replace(Trim(.Text), "'", "''") & "%"""
            Debug.Print l_strX
            MyRst.Filter = l_strX

Immediate window:
SiteName Like "widow%" --Works OK
SiteName Like "widow%" --Doesn't fail, but where did my space go?
SiteName Like "widow a%" --Fails with "3001" error.

lol - just noticed in the question.
"highly modified to fit my needs and programming standards".
Note to the casual reader: A "standard" is the set of preferences and conventions one employs when coding.
Alternative explanation - example code I provide to the masses sucks. lol

That'd be too much like an insult, and I would never purposefully insult you or anyone else (especially when they're giving out free code samples). The code was great, just the naming conventions aren't the same as my naming conventions (I'm real old and set in my ways) and I added some stuff to make it generic/polymorphic.

 

[Banana]

This made me wondered whether ADO used an escape character...

Apparently, it does...

In addition, you can use the "*" wildcard character in the search filter. However, the wildcard character cannot be used with Distinguished Name attributes (attributes of data type DN), such as the distinguishedName, memberOf, directReports, and managedBy attributes.

If the filter includes any attribute whose value has the "*" character, it must be replaced by the equivalent "\2A" to avoid having the "*" interpreted as the wildcard character. The backslash is an escape character and the ASCII hex equivalent for "*" is "2A".

So maybe you need to preface a space as "\ ", just like what you would do in a bash, or use ASCII hex for the space to get it to filter correctly?

 

[georgedwilkinson]

This made me wondered whether ADO used an escape character...

Apparently, it does...


So maybe you need to preface a space as "\ ", just like what you would do in a bash, or use ASCII hex for the space to get it to filter correctly?

I almost thought you were on to something:

            l_strSingleQuote = Chr(39)
            l_strX = "SiteName Like '" & Replace(Replace(Trim(.Text), "'", l_strSingleQuote), " ", "\ ") & "%'"
            Debug.Print l_strX
            m_rstSite.Filter = l_strX

Output:
SiteName Like 'widow%' --Good
SiteName Like 'widow%' --Good, but my space is gone. This is different than I thought.
SiteName Like 'widow\ a%' --Oh no, the "\" is now part of the lookup string. But no failure.

Alternate:

            l_strBackslash = Chr(92)
            l_strX = "SiteName Like '" & Replace(Replace(Trim(.Text), "'", l_strSingleQuote), " ", l_strBackslash & " ") & "%'"
            Debug.Print l_strX
            m_rstSite.Filter = l_strX

Gives (Same thing as using a regular backslash):
SiteName Like 'widow%'
SiteName Like 'widow%'
SiteName Like 'widow\ a%'

Alternate:

            l_strBackslash = Chr(92)
            l_strX = "SiteName Like '" & Replace(Trim(.Text), "'", l_strSingleQuote) & l_strBackslash & "%'"
            Debug.Print l_strX
            m_rstSite.Filter = l_strX

Gives (identical results using "*" instead of "%"):
SiteName Like 'wido\%' --No value in DDL, no failure.
SiteName Like 'widow\%' --No value in DDL, no failure.
SiteName Like 'widow\%' --No value in DDL, no failure, and my space is gone.
SiteName Like 'widow a\%' --My space is back! Alas, to no avail, no value in DDL.

I've tried just about everything everyone has suggested and there is always some scenario that the filter does not work under.

At this point, I've decided to abandon this route, though I'm still curious how something that doesn't work (can't filter on apostrophes and spaces concurrently) could be part of the system.

 

[Banana]

Is Chr() supposed to return a Hex representation of the character code?