OpenForm Filtering too much

[puzzledboy]

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

Can anyone help me?

Salut,


PB

 

[Steve Schapel]

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?

 

[puzzledboy]

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

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

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

 

[Steve Schapel]

PB,

Part of the problem here is your non-normalised data structure. It is
quite undesirable to be storing multiple values in a single field for
each record, such as "shoots leaves grubs bark". According to proper
database design principles, these should be listed as separate records
in a related table. This may seem at first glance to be more work, but
in fact would make your querying much simpler. So the first thing to
consider is whether a change in data model could be possible. Let us
know if you would like to explore this option further.

As regards the records with Nulls being returned unexpectedly, it is
possible, depending on how the data is being entered into these fields,
that there is a zero-length string "" rather than Null. It is simple to
test this, by making a query based on the table, and run it with ""
entered in the Criteria.

--
Steve Schapel, Microsoft Access MVP

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.

 

[puzzledboy]

Hi Steve,

Thanks for your engagement with this problem of mine. I really appreciate it.

Because I want to present some of this tomorrow (as a draft), I have opted
for a solution that is really pretty untidy: changing all the NULLS to a
single space character(" ") in the data table. This does the trick for now,
but its a bit of a lesson in how not doing things properly early on leads to
more and more working around. I will certainly look at changing the data
model soon.

[Perhaps I should explain that this database is really just a little
"playspace" we've carved off a much bigger database to fool around with
tagging, to find out if we can get the kind of reports we want, when we apply
different tagging structures. The UI probably doesn't make a lot of sense in
that context, but for senior management what they see IS the product, so I'm
putting a bit of work into it.]

For now, I'll leave off this thread -

Thanks for your responses


PB

PB,

Part of the problem here is your non-normalised data structure. It is
quite undesirable to be storing multiple values in a single field for
each record, such as "shoots leaves grubs bark". According to proper
database design principles, these should be listed as separate records
in a related table. This may seem at first glance to be more work, but
in fact would make your querying much simpler. So the first thing to
consider is whether a change in data model could be possible. Let us
know if you would like to explore this option further.

As regards the records with Nulls being returned unexpectedly, it is
possible, depending on how the data is being entered into these fields,
that there is a zero-length string "" rather than Null. It is simple to
test this, by making a query based on the table, and run it with ""
entered in the Criteria.

--
Steve Schapel, Microsoft Access MVP

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.

 

[Steve Schapel]

PB,

Access won't allow a single space character on its own in a field. As
soon as you leave the field, Access will trim the space out.

You need to identify the difference between Null and zero-length string,
though.

Anyway, I appreciate that you are focussing on a conceptual prototype at
the moment. But I would certainly urge you to institute a normalised
model for the next stage.