?
Solved

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

Posted on 2016-07-15
5
Medium Priority
?
70 Views
Last Modified: 2016-07-18
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
Comment
Question by:Declan_Basile
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 41713311
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41713318
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
 
LVL 85
ID: 41714502
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
 
LVL 1

Author Closing Comment

by:Declan_Basile
ID: 41717154
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 41718023
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question