Query by Form shows Null or Is Not Null

[Guest]

I have created a filter by form to provide data to a variety of Reports which
was working well - but now all but one of the fields shows Null or Is not
Null as the only options for their filter.

It is still possible to build a filter by typing in exact data or by using
wildcards in these fields, however until recently when selecting the drop
down arrow all possible entrys to the field were shown.

What has happened? Is there a limit to the number of options a field can
show in a QBF?

Thanks in anticipation.

 

[Dirk Goldgar]

I have created a filter by form to provide data to a variety of
Reports which was working well - but now all but one of the fields
shows Null or Is not Null as the only options for their filter.

It is still possible to build a filter by typing in exact data or by
using wildcards in these fields, however until recently when
selecting the drop down arrow all possible entrys to the field were
shown.

What has happened? Is there a limit to the number of options a field
can show in a QBF?

Yes, there's a limit, controlled by an option setting. It's on the
Edit/Find tab of the Tools -> Options... dialog: "Don't display lists
where more than this number of records read:".

 

[Guest]

Sorry for delay in response!

This setting seems to have fairly random results. I've tried various
settings on different forms of different complexities. On some occasions I
even get correct results (eg 150 unique values in the list) even though I
have set the setting to 1.

The form I am trying to get this to work on in particular admitedly is a
complex form based on a query that pulls together data from 5 tables, with
the form showing approx 40 fields. However even when I simplify the form I
still get strange results. (By simplify, I mean a form with only one field
from each table. These fields have amounts of unique values ranging from 6 to
150) The one with 6 values works ok until I put a second version of a field
that isn't working on the form. This new one will usually then work ok (and
also its original 'twin') - showing all its unique values. However the other
previously working field stops working!

Any thoughts!

 

[Dirk Goldgar]

Sorry for delay in response!

This setting seems to have fairly random results. I've tried various
settings on different forms of different complexities. On some
occasions I even get correct results (eg 150 unique values in the
list) even though I have set the setting to 1.

I've never really put that setting through its paces. I'm guessing --
just guessing -- that the setting may be factored to control how many
pages of records are fetched and examined, rather than controlling the
exact number of records. If this is so, then if reading one page gets
enough records to yield 150 unique values, so be it.

The form I am trying to get this to work on in particular admitedly
is a complex form based on a query that pulls together data from 5
tables, with the form showing approx 40 fields. However even when I
simplify the form I still get strange results. (By simplify, I mean a
form with only one field from each table. These fields have amounts
of unique values ranging from 6 to 150) The one with 6 values works
ok until I put a second version of a field that isn't working on the
form. This new one will usually then work ok (and also its original
'twin') - showing all its unique values. However the other previously
working field stops working!

I'm not sure I understand what you're describing, but it sounds strange.
Sure, if your form is based on a query involving 5 tables, I can see how
it might just read a calculated number of pages from each table to see
what unique values are there. But I don't see why duplicating a bound
control on the form, or even adding it twice to the query (without
adding another table) would have an effect. Would you be interested in
e-mailing me a sample of this? If so, post back letting me know and
I'll tell you where and how to send it.