Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Filter by Form behaving oddly?

Discussion in 'Information Technology' started by Jason M Owens, Jul 28, 2006.

  1. Jason M Owens

    Jason M Owens
    Expand Collapse
    Guest

    In a few databases at work, when I try & filter by form it doesn't behave as
    it should (or at least as I understand it should).

    When viewing a table in datasheet view I select the Filter by Form button &
    the table becomes just one empty row, as expected, with drop-down arrows in
    each field (as expected), for the user to select a value(s) on which to
    filter.

    However, when the drop-downs are clicked, instead of a list of the unique
    values within that field, there are just 2 options: Is Null & Is Not Null?

    Can anyone advise what is going on?

    Cheers,

    Jay
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Possibly some type of subtle corruption? Does this happen with all tables in
    the database, just one table, or a few tables? What happens if you create a
    new table and add some test data? Does it behave as expected?


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________


    "Jason M Owens" wrote:

    > In a few databases at work, when I try & filter by form it doesn't behave as
    > it should (or at least as I understand it should).
    >
    > When viewing a table in datasheet view I select the Filter by Form button &
    > the table becomes just one empty row, as expected, with drop-down arrows in
    > each field (as expected), for the user to select a value(s) on which to
    > filter.
    >
    > However, when the drop-downs are clicked, instead of a list of the unique
    > values within that field, there are just 2 options: Is Null & Is Not Null?
    >
    > Can anyone advise what is going on?
    >
    > Cheers,
    >
    > Jay
    >
    >
     
  4. vikenk@aol.com

    vikenk@aol.com
    Expand Collapse
    Guest

    Jason M Owens wrote:
    > When viewing a table in datasheet view I select the Filter by Form button &
    > the table becomes just one empty row, as expected, with drop-down arrows in
    > each field (as expected), for the user to select a value(s) on which to
    > filter.
    >
    > However, when the drop-downs are clicked, instead of a list of the unique
    > values within that field, there are just 2 options: Is Null & Is Not Null?


    This also happens to me when using a form. When in the Main Form, I
    select "Filter by Form" and a blank form shows up, as expected. I also
    get the drop-down arrows, as expected, and I get two values - Null or
    Not Null. I didn't realize that the data was supposed to be in the
    drop-down box.

    When I manually input the data, it works.

    --
    Viken K.
    http://www.vikenk.com
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Viken,

    Your post prompted me to experiment a bit more with filter by form. I don't
    tend to use it myself. It is disabled in databases that I distribute to
    users, because I think it's just too confusing for the average user. A few
    minutes of frustration using the built-in Filter by Form confirms to me why I
    invested the time a few years ago to learn the more powerful Query by Form
    (QBF) technique, where one builds the WHERE portion of a SQL statement
    on-the-fly, using VBA code. I can send you a sample database,if you are
    interested in seeing a better mouse trap. If you are interested, send me a
    private e-mail message with a valid reply-to address.

    My e-mail address is available at the bottom of the contributor's page
    indicated below. Please do not post your e-mail address (or mine) to a
    newsgroup reply. Doing so will only attract the unwanted attention of
    spammers.

    I found some interesting results using the Orders and Order Details tables
    in the sample Northwind database. First, lookup fields can be problematic.
    This is confirmed in the following KB article (I added the parenthesis around
    the title of this article, because it likely applies to Access 2002 and 2003
    equally well):

    (ACC2000:) Filter By Form Options Do Not Apply to Lookup Fields
    http://support.microsoft.com/?id=208573

    Try applying a filter to the Employee field in the Orders table. This is a
    nasty lookup field. You will receive an error message. This KB article
    includes the following paragraph in the section titled More Information:

    <Begin Quote>
    "If the form's recordset contains more records than the number specified on
    the Edit/Find tab of the Options command, then Microsoft Access does not fill
    the combo box or list box with unique values from the form's recordset.
    Rather, it fills the list with two values: Is Null and Is Not Null. The only
    exception is when a field is a Lookup field (that is, when the field has its
    DisplayControl property set to combo box or list box in an underlying table).
    Then, Microsoft Access uses the table's RowSource property setting to fill
    the value list for a Filter By Form combo box or list box, instead of reading
    records from the form's recordset."
    <End Quote>


    In the Order Details table, I do indeed get Is Null and Is Not Null as the
    only choices for the (5) fields. This table has over 2000 records in my copy
    of Northwind. The setting that I had, under Tools > Options | Edit/Find tab
    for the "Don't display list where more than this number of records read:"
    setting was set to 1000. So, the result is consistent with the KB article.

    I added a new text data type field to the Order Details table. I entered
    "Joe", "Fred", "Barney", and "Tom" into the first four records. Select Filter
    by Form on this field, and you get Is Null and Is Not Null as your only
    choices. Now, go back into table design view, and set an Index (Duplicates
    OK) on this field. Repeat the filter by form test, and you will likely see
    that you get a filtered list that includes five records, one that looks blank
    (represents most of the records, where you did not enter anything) and four
    records with the unique names that you entered. However, now go back into
    table design view, and change that Index to No Duplicates. Apply a Filter by
    Form. The resulting combo dropdown appears to include one blank row for each
    record in the table that is null. Not terribly useful.

    By the way, if that was your *real* e-mail address that you included, you
    should re-configure your newsreader so that it includes a munged form of your
    e-mail address. Otherwise, you are extending a welcome mat to all the
    spammers who harvest e-mail addresses from newsgroup posts. I removed your
    e-mail address in this reply.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "vikenK@<removed>" wrote:

    >
    > Jason M Owens wrote:
    > > When viewing a table in datasheet view I select the Filter by Form button &
    > > the table becomes just one empty row, as expected, with drop-down arrows in
    > > each field (as expected), for the user to select a value(s) on which to
    > > filter.
    > >
    > > However, when the drop-downs are clicked, instead of a list of the unique
    > > values within that field, there are just 2 options: Is Null & Is Not Null?

    >
    > This also happens to me when using a form. When in the Main Form, I
    > select "Filter by Form" and a blank form shows up, as expected. I also
    > get the drop-down arrows, as expected, and I get two values - Null or
    > Not Null. I didn't realize that the data was supposed to be in the
    > drop-down box.
    >
    > When I manually input the data, it works.
    >
    > --
    > Viken K.
    > http://www.vikenk.com
     
  6. Jay

    Jay
    Expand Collapse
    Guest

    Thanks for that Tom...It was really puzzling me why it worked it some
    databases and not in others - I only tend to use it now & again to save
    building a query if it's just a one-off & the criteria are simplistic enough
    to allow it.

    Regards

    Jay




    On 2/6/06 15:51, in article
    03F834F0-EE6D-480F-9A04-882D4BF6AF7B@microsoft.com, "Tom Wickerath" <AOS168b
    AT comcast DOT net> wrote:

    > Hi Viken,
    >
    > Your post prompted me to experiment a bit more with filter by form. I don't
    > tend to use it myself. It is disabled in databases that I distribute to
    > users, because I think it's just too confusing for the average user. A few
    > minutes of frustration using the built-in Filter by Form confirms to me why I
    > invested the time a few years ago to learn the more powerful Query by Form
    > (QBF) technique, where one builds the WHERE portion of a SQL statement
    > on-the-fly, using VBA code. I can send you a sample database,if you are
    > interested in seeing a better mouse trap. If you are interested, send me a
    > private e-mail message with a valid reply-to address.
    >
    > My e-mail address is available at the bottom of the contributor's page
    > indicated below. Please do not post your e-mail address (or mine) to a
    > newsgroup reply. Doing so will only attract the unwanted attention of
    > spammers.
    >
    > I found some interesting results using the Orders and Order Details tables
    > in the sample Northwind database. First, lookup fields can be problematic.
    > This is confirmed in the following KB article (I added the parenthesis around
    > the title of this article, because it likely applies to Access 2002 and 2003
    > equally well):
    >
    > (ACC2000:) Filter By Form Options Do Not Apply to Lookup Fields
    > http://support.microsoft.com/?id=208573
    >
    > Try applying a filter to the Employee field in the Orders table. This is a
    > nasty lookup field. You will receive an error message. This KB article
    > includes the following paragraph in the section titled More Information:
    >
    > <Begin Quote>
    > "If the form's recordset contains more records than the number specified on
    > the Edit/Find tab of the Options command, then Microsoft Access does not fill
    > the combo box or list box with unique values from the form's recordset.
    > Rather, it fills the list with two values: Is Null and Is Not Null. The only
    > exception is when a field is a Lookup field (that is, when the field has its
    > DisplayControl property set to combo box or list box in an underlying table).
    > Then, Microsoft Access uses the table's RowSource property setting to fill
    > the value list for a Filter By Form combo box or list box, instead of reading
    > records from the form's recordset."
    > <End Quote>
    >
    >
    > In the Order Details table, I do indeed get Is Null and Is Not Null as the
    > only choices for the (5) fields. This table has over 2000 records in my copy
    > of Northwind. The setting that I had, under Tools > Options | Edit/Find tab
    > for the "Don't display list where more than this number of records read:"
    > setting was set to 1000. So, the result is consistent with the KB article.
    >
    > I added a new text data type field to the Order Details table. I entered
    > "Joe", "Fred", "Barney", and "Tom" into the first four records. Select Filter
    > by Form on this field, and you get Is Null and Is Not Null as your only
    > choices. Now, go back into table design view, and set an Index (Duplicates
    > OK) on this field. Repeat the filter by form test, and you will likely see
    > that you get a filtered list that includes five records, one that looks blank
    > (represents most of the records, where you did not enter anything) and four
    > records with the unique names that you entered. However, now go back into
    > table design view, and change that Index to No Duplicates. Apply a Filter by
    > Form. The resulting combo dropdown appears to include one blank row for each
    > record in the table that is null. Not terribly useful.
    >
    > By the way, if that was your *real* e-mail address that you included, you
    > should re-configure your newsreader so that it includes a munged form of your
    > e-mail address. Otherwise, you are extending a welcome mat to all the
    > spammers who harvest e-mail addresses from newsgroup posts. I removed your
    > e-mail address in this reply.
    >
    >
    > Tom Wickerath
    > Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "vikenK@<removed>" wrote:
    >
    >>
    >> Jason M Owens wrote:
    >>> When viewing a table in datasheet view I select the Filter by Form button &
    >>> the table becomes just one empty row, as expected, with drop-down arrows in
    >>> each field (as expected), for the user to select a value(s) on which to
    >>> filter.
    >>>
    >>> However, when the drop-downs are clicked, instead of a list of the unique
    >>> values within that field, there are just 2 options: Is Null & Is Not Null?

    >>
    >> This also happens to me when using a form. When in the Main Form, I
    >> select "Filter by Form" and a blank form shows up, as expected. I also
    >> get the drop-down arrows, as expected, and I get two values - Null or
    >> Not Null. I didn't realize that the data was supposed to be in the
    >> drop-down box.
    >>
    >> When I manually input the data, it works.
    >>
    >> --
    >> Viken K.
    >> http://www.vikenk.com
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Jay,

    You're welcome. Attempting to answer this question was a learning experience
    for me, too.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Jay" wrote:

    > Thanks for that Tom...It was really puzzling me why it worked it some
    > databases and not in others - I only tend to use it now & again to save
    > building a query if it's just a one-off & the criteria are simplistic enough
    > to allow it.
    >
    > Regards
    >
    > Jay
     
  8. Jay

    Jay
    Expand Collapse
    Guest

    Hi Tom,

    I've just had chance to study your post in more detail & appreciate the
    time you must have put into it. I've bookmarked the KB article for a read
    later, but haven't had much need of lookup fields yet, so will save it for
    if & when I do:)

    My tables affected just needed the Indexed property setting to Yes
    (Duplicates OK) to make the filter by form work - just as you said:) What
    I can't understand is this:- Microsoft tout the Filter by Form (taking just
    table datasheet view as an example) as a standard tool - all the Microsoft
    Press books I've read/owned teach this as a basic technique...but *never*
    mention that the fields(s) must be indexed. And as the Yes (Dupl' OK) isn't
    the default property in a new table designed from scratch I expect a few
    people must have tried filtering by form & been confused why it didn't work.
    (Although I find it interesting that Indexes *are* set automatically by the
    Table wizard)

    So that's why the tables I was dealing with at work had the null/not null
    filter_by_form problem - they had all been set up from scratch and therefore
    hadn't had the fields indexed by the table wizard. I think in future I'll
    try get into the habit of indexing all fields in new tables (and let my
    colleagues know also).

    Many thanks for what has turned out to be a very informative process:)

    Jay
    ___


    On 3/6/06 11:30, in article
    A7ED6B28-F705-49F7-A34E-F0C9CFFCC9D5@microsoft.com, "Tom Wickerath" <AOS168b
    AT comcast DOT net> wrote:

    > Hi Jay,
    >
    > You're welcome. Attempting to answer this question was a learning experience
    > for me, too.
    >
    >
    > Tom Wickerath
    > Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "Jay" wrote:
    >
    >> Thanks for that Tom...It was really puzzling me why it worked it some
    >> databases and not in others - I only tend to use it now & again to save
    >> building a query if it's just a one-off & the criteria are simplistic enough
    >> to allow it.
    >>
    >> Regards
    >>
    >> Jay
     

Share This Page