Query by Form shows Null or Is Not Null

Query by Form shows Null or Is Not Null

Post by Sm9ob » Tue, 11 Apr 2006 08:07:02


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.
 
 
 

Query by Form shows Null or Is Not Null

Post by Dirk Goldg » Tue, 11 Apr 2006 10:44:54


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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 
 
 

Query by Form shows Null or Is Not Null

Post by Sm9ob » Fri, 28 Apr 2006 08:01:02

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!
 
 
 

Query by Form shows Null or Is Not Null

Post by Dirk Goldg » Sun, 30 Apr 2006 02:20:46


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.


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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)