IsNull/Is Not Null (WIN 2000 Acc 97)

Topic

New Reply

I asked this a long time ago and got a couple of responses – but none were THE answer.
Hopefully someone here will recognize the situation and say “Oh yeah, it’s because….”

I have a bound form with a bunch of textboxes.
When I right click on the form and select Filter by Form,then click in one of the textboxes, it offers me IS Null and Is Not Null. But when I click in a different textbox, it offers me everything that was ever entered in the text box. This happens with all of the textboxes with no pattern that I can determine. All textboxes have the same properties. Why would some offer Is Null and IS Not Null, while others offer me everything?

I posted this elsewhere and received a few responses :
Here’s a summary of what it ISN’T so far:

1) Not the index
2) Not unique values
3) Not joins
4) Not null values

DateIn =
1)Indexed,Dups OK
2)Many Unique values
3)No null values
4)Not joined with other tables

DateOut =
1)Indexed,Dups OK
2)Many Unique values
3)Null values
4)Not joined with other tables

LastName = (same as DateIn)

DateIn offers me every unique entry
LastName offers me Is Null/Is Not Null

Where else should I be looking?

Thanks for reading this!!

Michael

Reply | Quote

Replies

I’ve noticed this behavior most commonly with linked data sources. And, so far, have not been able to find a way around it.

Reply | Quote

But why would some fields offer Is Null/Is Not Null when Filtering by Form and others offer all unique entries?
The form is bound to a table(FE/BE).

This seems to be another quirk of Access that should be able to be explained, but just can’t, I guess !!

Thanks PaulK for your reply !

Michael

Reply | Quote

The behavior I’ve observed with filter by form is that fields that are indexed show a list of unique values. Fields that are not indexed show Is Null and Is Not Null.

Reply | Quote

Charlotte:
DateIn(Indexed) offers me every unique entry
LastName(Not Indexed) offers me Is Null/Is Not Null

????????????

A real head scratcher !!!

Michael

Reply | Quote

The behavior you observe is what MS would call “by design.” From the Access Help file ( “Troubleshoot filters” topic):
[indent]

---

I don’t have lists of values to pick from on one or more of the fields in the Filter By Form window.

How many records Microsoft Access reads depends on whether or not the field is indexed. If the field is indexed, Microsoft Access reads only the unique values, not all the values in a field. If the field isn’t indexed, Microsoft Access reads all the values in the field. If the number of records it reads is more than the maximum it’s allowed to display, which is determined by the setting for Don’t Display Lists Where More Than This Number Of Records Read, Microsoft Access doesn’t display the values for that field on the list. Consequently, you need to change the setting for this option. Because Microsoft Access reads all values in nonindexed fields, not just unique values, set this option to a number greater than (or equal to) the number of records in the nonindexed field with the most values.
Note When Microsoft Access is able to display the list in a field, it only shows the unique values, even for a nonindexed field.

---

[/indent]
HTH??

Reply | Quote

Forcing the system to return unique values for a non-indexed field means slowing down then whole thing. Frankly, I find filter-by-form to be of limited use in a large application.

Reply | Quote

As far as I know, this is how it works:

If the original control is a combo box, Access uses the row source of the combo box to fill the dropdown list in Filter by Form.

If the original control is a text box, the dropdown list in Filter by Form is governed by a combination of several settings:

• The settings in Tools/Options…. Edit/Find tab:
  • Whether to display lists for indexed fields and for non-indexed fields.
  • The max number of records to read. For indexed fields, only unique values are counted; for non-indexed fields, all records are counted.
    Example: say the number is set to 100. You have 200 records, but the field FirstName contain only 25 distinct values. If FirstName is indexed, the dropdown list will display the 25 distinct names. But if FirstName is not indexed, the dropdown list will only display Is Null/Is Not Null, because it would have to read more than the maximum number of 100 records to fill the list.
    [/list]
  • The FilterLookup property of the text box:
    • Default = use settings from Tools/Options… (see above).
    • Always = always display values.
    • Never = only display Is Null/Is Not Null.
      [/list]
    • Whether the bound field is in a linked table or not. The dropdown list for non-indexed fields in linked tables will display Is Null/Is Not Null even if Non-indexed fields is checked in Tools/Options…, unless FilterLookup is set to Always.
      [/list]Confusing, isn’t it?

Reply | Quote

First off, I would like to thank you guys/gal for your time in answering my question.
I read and re-read all of the suggestions, and did find the solution amongst all of this great information (and some stuff
I can use in the future as well !!).

On the surface, it appears that the DateIn and LastName textboxes have all of the same characteristics. Both are indexed, neither has Null values, neither are linked.

The solution was
Tools/Options…. Edit/Find tab
The max number of records to read. For indexed fields, only unique values are counted
The setting is at 1,000
DateIn (Year 2002 only, has less than 1,000 unique values)
LastName – has way over 1,000 unique values
BINGO !!!

I am going to leave it at 1,000, but I did experiment and changed the number to 30,000 and the LastName textbox did
offer all unique values as expected.
Also, if I need to override the 1,000, I can use the FilterLookup property of the given textbox. I have always known the property was there, but never knew what the heck it did. (Now I do !!!)

Again, thanks to you all for helping me learn a bit more about this wonderful Access !!

Have a good weekend!!

Michael Abrams

Reply | Quote