Re: OpenForm Filtering too much

Hi Steve,

Many thanks for the speedy reply. I will try to describe what I am doing
and what is going on a bit better.

First of all, the reason I'm using LIKE is that the field may contain a
string of several tags, in which I want to search: the field [diet] actually
contains the text "shoots leaves grubs bark", and I just want to find, for
example, all leaf eaters.

The filter text is built up in a text box in the form SearchBuilder as
described in my first post, and when satisfied with the text, the user hits
"RunIt!" to open ShowResults. ShowResults is a form built on a table, rather
than a query. So RunIt (macro) uses the OpenForm command with the following
parameters
form: ShowResults
view: form
filtername:
where condition: =[Forms]![SearchBuilder]![ConditionText]
Data Mode:
Window Mode: normal

for reference:
[Forms]![SearchBuilder]![ConditionText] = ("(["+StringFromGUID([Field])+"]
"+StringFromGUID([Comparator])+" '*"+StringFromGUID([FieldValue])+"*'"+")")

The ShowResults form shows Null results for searches in some fields: for
example,
I search on ([diet] LIKE "*grubs*") and get four results.
I search on ([diet] NOT LIKE "*grubs*") and get 650 results and no NULL
values. This is fixed with an OR clause as you suggest - thanks again -
although I only really want NULLS to show up for the NOT LIKE case...

The weird part is that for some searches, the NULLS that matter seem to be
in another column. I search on [habitat] LIKE "*mountains*" I get say 10
results.
For [habitat] NOT LIKE "*mountains*" I get 644 results that include NULL
values (note that the total is still 654 / about 1900 total entries) and this
seems to be because there are no NULLS in the field [diet], even though it is
not referred to in the form filter property. Since the underlying data
source is a table, I can't think where this filtering on [diet] would be
coming from.

Let me know if there's anything you want me to try - or if you think I'm
doing this a bit ass-backward given my overall goal (can I say ass on this
board?) I'm happy to try a redesign.

cheers,

PB


"Steve Schapel" wrote:

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...

.