using not and wildcards in adodb recordset

[CJ_London]

I have a form which is populated with a disconnected ADO recordset. In this particular application, I can't use a DAO recordset.

I'm trying to set up filter criteria and have hit a problem - see this link for background on ADO filtering

http://msdn.microsoft.com/en-us/library/windows/desktop/ms678073(v=vs.85).aspx

The problem is that I can set these filters

"Fld Like '*somevalue*'" i.e. return when somevalue is somewhere in fld
"Fld Like 'somevalue*'" i.e. return when somevalue is at the beginning of fld

But this doesn't work
"Fld Like '*somevalue'" i.e. return when somevalue is at the end of fld

According to the link this is supposed to be the case - you can have an * at the end, or the beginning and end, but not just the beginning.

Similarly, the 'Not' operator doesn't appear to work - not even mentioned in the link!

"Fld Not Like '*somevalue*'" i.e. return when somevalue is nowhere in fld

And although I'm not using it the ? wildcard doesn't appear to be available either.

So my question is - does anyone have any suggestions for a filtering workaround - without requerying the underlying recordset and applying the filter as criteria which has performance issues.

Thanks in advance

 

[pbaldy]

In ADO you want % as a wildcard rather than *.

 

[CJ_London]

I thought so originally and I'm sure I've seen somewhere that is what you are supposed to use. It does work using '%' instead of '*' but with the same limitation -generates error 3001: arguments are of the wrong type if there is not a '%' at the end of the string.


I started off using '%', got the error, went on line and found the article, moved to '*' and didn't move back

I've also tried using Alike rather than Like (aka ANSI92) but this also generates the 3001 error whether you use % or *.

Only thing I haven't tried is converting the db to ANSI92, guess I'll take a copy and see if it works for this - if it does, got a lot of queries to modify!

 

[Galaxiom]

One solution would be to derive a field with the string reversed in the original query and filter that with Like.

BTW. Have you tested the requery performance? It is often surprising. I had a case where I repeatedly applied Filter and Find to an ADO recordset in a loop then compared it with requerying. The loop with requerying was so much faster that I thought the sub had failed completely when it finished in five seconds instead of over a minute.

I would also add the following detail which they don't make clear in the link.

In ADO filters, only one comparison operator can be used. For example, the following expression is not supported because it uses two different comparisons.

[fld1] = 2 AND [fld2] > 4

But this is fine:
[fld1] > 2 AND [fld2] > 4

 

[CJ_London]

I haven't properly tested requery as yet because the initial data takes about 7 or 8 seconds to load - it is a navigation subform with about 100,000 records based on a nested set tree and associated records. If I do a partial load, it still take 5 or 6 seconds. Whereas a filter is taking less than a second. However the load is only done the once when the form is opened so is an acceptable overhead. Also, I would need to preserve the tickbox value which may be an added complication.

If I load as a DAO recordset it is a bit quicker because I guess the data only gets loaded once, however for my requirements I need to open the DAO recordset and then copy it to a disconnected ADO recordset which contains a tickbox select field - a bit like the multivalue fields available in 2007 and later or the unhide shortcut menu option. If I create a simple ado recordset which is as fast as DAO, then disconnect it, I can't then add the tickbox field.

What the user wants to be able to do is to find some records based on one or more values (using filtering and sorting) on any level of the tree and then use the tickbox to highlight which records they are interested in (in the same way a simple multiselect listbox works) - these are then displayed in another subform.

So for example they may want to compare details on a number of companies. So they filter/sort the list to find the first company - might be on name and/or other metrics. then tick the boxes for the ones they want to see (populating the other subform) then they filter and sort again to find the next company etc. The filter includes the tickbox so they always show if ticked - the user can then untick a company (and therefore remove from the other subform) if they find a better match.

Simple =, <>, >=, <= filtering works great as does sorting but for example if they want to find all records for BT, which is in the data multiple times as BT, B T, BT PLC, BT Ltd, BT Holdings etc they want to be able to highlight BT and select all records that begin with BT - this also works great. The problem comes when they want to find records which ends with something - for example ends with Gmbh (although I accept this particular example will probably work just as well with *Gmbh* which works) or alternatively they want to exclude companies with 'telecom' in their name.

Guess I've been spoilt with DAO filtering!

Incidentally I have not experienced your point about mixing comparisons

for example this filter works in ADO

"[ticked]=-1 OR [CName]<>'somename' AND [Country] = 'UK' AND [Turnover]>500000"

However as writing this, I think I have found a solution - use of bookmarks. Something for me to test but suspect it may be too slow since it has to parse through the recordset. May be able to filter first to reduce the record set and then parse through that instead.

 

[Galaxiom]

IIncidentally I have not experienced your point about mixing comparisons

for example this filter works in ADO

"[ticked]=-1 OR [CName]<>'somename' AND [Country] = 'UK' AND [Turnover]>500000"

This has me quite bewildered. I certainly can't replicate it now. Nor can I find anything online (except where I have posted it before )

The original issue was a long time ago but I distincly remember having a problem and finding a description of the issue. It was a surprising limitation but it matched my problem.

I then tried a workaround with a Filter followed by a Find. Ultimately I replaced that with opening a new recordset each time using the new criteria. It performed better so I stayed with that.

 

[Galaxiom]

BTW. What did you think of the idea of reversing the string? (And of course the search string too.)

 

[CJ_London]

Re mixing comparisons I have found that you can't use two comparisons on the same field e.g.

CName like '*west*' AND CName='WESTERN isle'

Which can happen if the user has used the first filter to shorten the list then from that shortened list selected a specific name with the second filter - I was working on the principle of using multiple filters so may need to modify to only use the latest filter for a particular field - or maybe I've got my ands and ors mixed up.

So having to find a workaround anyway!

Working on it tomorrow, I'll let you know how I get on.

Re reversing the strings, I think the only way I can do that is to have extra hidden field(s) where the name(s) are reversed - the filter is built in vba so I can reverse the selected text (I use a shortcut menu which replicates the standard DAO sort/filter shortcut menu but works off ADO recordsets) and filter against this field rather than the one used to select the text.

Alternatively I just don't provide it as an option - they can use *XXX* instead.

With what I have provided at the moment, they can typically find what they want by entering three of four characters (list updated after each character entered) and perhaps a sort. So I might be moving into the 'because its there' territory anyway

 

[CJ_London]

Well I've finished my little investigation of building ADO filters.

I did discover a couple of unexpected things

• if fld1 is numeric then
  • [fld1]='123' works as a filter in ADO (but doesn't in DAO)
• if fld1 is text then
  • [fld1] like 'something'
    • works the same as
      • [fld1] like '*something*'

In summary the filtering options in ADO are much more restrictive than DAO - for example:

• the Not operator doesn't exist (e.g. Not Like)
• the In operator does not exist
• neither can you filter on [Fld1]='*something' (ends with)
• you cannot apply any sort of function in the filter (e.g. nz, instr, abs, iif)
• you cannot combine fields to filter (e.g.[fld1]/[fld2]>1)
• Or's can only be applied at the highest level e.g.
  • [Fld1]=0 OR ([Fld2]='A' AND [Fld3] Like 'B') works
  • ([Fld1]=0 OR [Fld2]='A') AND [Fld3] Like 'B' doesn't - it has to be written as
    • ([Fld3] like 'B' AND [Fld1]=0) OR ([Fld3] Like 'B' AND [Fld1]='A')

With regards performance, I was working with a dataset of circa 100,000 records with 6 columns plus a 'tick' column in a disconnected recordset. The recordset takes 6 seconds to load initially (it pulls data from a number of different tables with a number of calculations and subqueries) but once loaded filtering and sorting were significantly less than a second, certainly well within user acceptance levels.

 

[Galaxiom]

I just stumbled across the reference to the mixed operator limitation for ADO Filters. I wasn't completely mad after all.


"The Criteria argument can be a two-condition with the following restrictions:

• If the column name (the database field) is the same in both clauses, then the separate where clauses must define a contiguous range.
• If the column names are different, then the operators must be the same. If the operators are "LIKE", then the filtered region may be unexpected."

"The Criteria argument can be three or more conditions with the following restrictions:

• The operators must be the same for all conditions. If the operators are "LIKE", then the filtered region may be unexpected"

http://msdn.microsoft.com/en-us/library/ee275540(v=bts.10).aspx

 

[CJ_London]

it is confusing

But with some creative structuring I was able to meet my objectives (except the NOT LIKE) so this works for example

([Fld3] like 'B' AND [Fld1]=0) OR ([Fld3] Like 'B' AND [Fld1]='A')

A worthwhile exercise from my perspective and client is happy which is the main thing

 

[Galaxiom]

Although I have not tested it, I expect the limitatation was on the Version 2 libraries (Reference: Microsoft ActiveX Data Objects 2.x Library) but overcome in the 6.x libraries on Windows 7.

When I experienced the problem I was on XP where there was no 6.x library available.