Re: OpenForm Filtering too much

PB,

What is the reason for the Like operator? Wouldn't it be what you really want:
[diet]='shoots'

Anyway, as regards Nulls, you need to specifically handle them in the query criteria. A couple of examples:
[diet]<>'shoots' Or [diet] Is Null
Nz([diet],"")<>'shoots'

If that doesn't help, can you post the full SQL view of the query?

--
Steve Schapel, Microsoft Access MVP

puzzledboy wrote:
Hi all,

I'm developing a simple front-end for re-organising information based on some text tags (for example, animals might have tag fields for size with options big, medium, small; another tag field for [diet] with options roots, shoots, leaves.) The way I'm doing this it to let the user choose from a list of field names, and then the tag of interest, in a form. They can choose 'LIKE' or 'NOT LIKE' for the comparator. The SearchBuilder form then takes this and makes it a "where" condition:
[diet] LIKE '*shoots*'

The user then hits a button which calls a macro which opens the report form with detail rows for every animal with that tag - e.g. any that eat "shoots".

ATM this is not super-useful, but I'm planning to add the ability to append several clauses to allow a good versatile multi-dimensional search that is easier for the novice than QBE or SQL.

The PROBLEM: there are about 1900 records. The LIKE queries correctly collect the wanted entries. The NOT LIKE queries should be the complement of the LIKE queries, but many rows are not turning up. It appears to be that NULL values in one field are causing that record to be filtered out. But it isn't the field that I am filtering on. I can't find anything that would cause this extra filtering...
.

Loading