Solved

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

Posted on 2016-07-15
5
37 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
  • 3
5 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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 84
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now