Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IsNull/Is Not Null (WIN 2000 Acc 97)

    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

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull/Is Not Null (WIN 2000 Acc 97)

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

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull/Is Not Null (WIN 2000 Acc 97)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IsNull/Is Not Null (WIN 2000 Acc 97)

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

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull/Is Not Null (WIN 2000 Acc 97)

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

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

    A real head scratcher !!! <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Michael

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IsNull/Is Not Null (WIN 2000 Acc 97)

    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:
    <UL><LI>The settings in Tools/Options.... Edit/Find tab:
    <UL><LI>Whether to display lists for indexed fields and for non-indexed fields.
    <LI>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]<LI>The FilterLookup property of the text box:
    <UL><LI>Default = use settings from Tools/Options... (see above).
    <LI>Always = always display values.
    <LI>Never = only display Is Null/Is Not Null.[/list]<LI>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?

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: IsNull - Filter by Form (WIN 2000 Acc 97)

    The behavior you observe is what MS would call "by design." From the Access Help file ( "Troubleshoot filters" topic):
    <hr><font color=blue>I don't have lists of values to pick from on one or more of the fields in the Filter By Form window.</font color=blue>

    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.<hr>
    HTH??

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IsNull - Filter by Form (WIN 2000 Acc 97)

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

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IsNull/Is Not Null (WIN 2000 Acc 97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •