Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-07-15
5
Medium Priority
?
85 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 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

782 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