Solved

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

Posted on 2016-07-15
5
42 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

932 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

10 Experts available now in Live!

Get 1:1 Help Now