Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 117
  • Last Modified:

Datasheet Filter in Access 2016 to only show records with blanks in a field.

In Access 2016, if you're in datasheet view of a form and the number of records read is too much to show a list of values to filter on for a field, not only do the values to filter on not appear but also the "blanks" checkbox does not appear as a filter option.  In this circumstance, how can a user EASILY set a filter to show only records with blanks for that field without having to type an expression like "([Addresses].[POBox] Is Null OR [Addresses].[POBox]="") in the filter by form?  I need to make this easy for the user.  Also, I don't want to increase the option value for "Don't display lists when more than this number of records is read" because there are over 40,000 records.
0
Declan_Basile
Asked:
Declan_Basile
  • 3
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
The kicker here is that (depending on the source data) , ...a "Blank" may be Null, or a zero length string("")
...so at some level you may need to have both in your filter,....

You could probably create some type of NZ() function to convert nulls to something else though...

But 40000 records is a lot to filter through...

Consider limiting the recordsource.
For example, ...instead of listing ALL customers, ...list only active customers, ...or customers wh have only placed orders in the last 12 months.

Instead of listing ALL products, ...list only "Active" products

Instead of listing ALL employees, ...list only "Sales" employees...
0
 
Jeffrey CoachmanMIS LiasonCommented:
As convenient as "Filter by form" is.... as you can see, ...there are some criteria that are not so simple.

Consider creating your own custom filtering system, ...where custom criteria like:
"([Addresses].[POBox] Is Null OR [Addresses].[POBox]="") i
...can be applied behind the scenes.,
But in your user interface, the only thing the user selects is "Blanks"
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd agree with Jeff - if you have needs more complex than those provided by the built-in systems, then you should create your own filtering system. You can make that as simple or as complex as you wish, and you can tailor the system to exactly meet your needs. You won't be able to do that with the built-in systems.
0
 
Declan_BasileITAuthor Commented:
Thanks Jeff.  I was hoping that there was something I was missing, like an option to always show the "blank" option even if the list of other values aren't shown because the quantity of them is over the threshold to show them.  I'm trying to use the built in functions as much as possible, but I'll have to, as you said, create my own feature for including/not including blanks.  It still beats confusing the heck out of the users by telling them about null/empty string syntax.
0
 
Jeffrey CoachmanMIS LiasonCommented:
You you are correct.

You should always use the tools that Access provides, ...if at all possible.
Too many people ask to build their own custom filters when Access already provides this basic functionality.
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now