Quick filter in datasheet is missing

Question

I have a subform in datasheet view mode and it has missing the filter option that generally appear with checkboxes.

Missing

By qick filter and checkboxes for filter I mean this:

Can somebody tell me how can I get that back?

Politesse

Answer 1

The form's data source query had a filter parameter set which was causing recursive filtering. When I remove that filter ( as it was of no use) it worked well. It was like a logical error which created that issue.

Politesse

Answer 2

Today I stumbled upon a possible answer for you. Try this option:

Menu File → Options... → Filter lookup options for <your DB name> Database

Change the option Don't display lists when more than this number of records is read: from 1000 (the default) to some larger number suiting your needs.

1. Go to "Options"
2. Find "Current Database"
3. Scroll down until you find "Filter Lookup options for {.... your file name...}. It will show the list, and it doesn't allow you to do any filter if your data entry over 1,000. You just select ODBC fields and set the entry as you wish, for example, 2,000.

That's all.

Waisted

I had different issue and I have posted it and marked as answer. – Politesse

This is the proper answer to the question ! For me, activating the ODBC fields did the trick (I have a SQL Server backend) – Arsenate

Is it really spelled "OPTION"? Not "Option" or "Options"? – Shinn

That did not help me. – Perimeter

Answer 3

None of the answers above are correct if you are looking to solve the question initially listed. After having toyed with this issue in my own database, I found Access no longer appears to support cross-referenced queries with linking fields (e.g. Using a drop-down list with a common name [text] that links to a field within a main table using numbers to reduce the record/database size). I found by adjusting the size of the index field from 0 to .0202 it would allow the filter mechanism to work again, but automatically lists the numbers in the field instead of the common names associated. It appears the only way to fix this issue is to change your relational linkage from numeric to text and use the common name as the actual entry (Monumentally erroneous change in my opinion). Hopefully they will fix it. In the meantime I will look for a better answer. If I find one, I'll post it here. -DFoxII

Mitrailleuse

I did verify the issue here and found the best manner to fix it is to replace index fields with text entry. Unfortunately this means your database will grow in size much quicker than before depending on how many fields you have to replace. You will have to decide whether or not this feature makes it all worth while. Good Luck All! – Mitrailleuse

What do you mean you verified the issue? Did you ask Microsoft? – Truc

Answer 4

The listed answers didn't solve my specific issue. I too was not getting the filter lists to appear on subforms.

After much banging my head, I found out that my main form had "Allow Filters" set to No. Even though the subforms all had Allow Filters turned on, it appeared they inherited the main form's setting.

Basil

Answer 5

I had the same problem and after tried different ways (including the ones mentioned here), and I think I finally got it to work.

First, make sure you change the option as Brad said on his answer. I have a combo box with two columns, ID and CustomerName. In the beginning, the row source for this combo box is: SELECT ID AS CustomerID, CustomerName FROM tblCustomers ORDER BY CustomerName; The checkbox for the filter did not show.

I found that there are two problems with this query for the combo box row source:

1. You can't use alias for the ID column.
2. You can't do sorting on the second column (the CustomerName here).

So after I change the query to: SELECT ID, CustomerName FROM tblCustomers; The checkbox came back! (you can do sorting on the first column though)

Quotation

Answer 6

This is an old question with lots of answers. None of those worked form me with Access 2016. Only after I changed the datasource of form from an SQL statement to a simple name of Access query it worked and the quick filter reappeared.

Perimeter

Answer 7

I do believe that this is caused by a large amount of non-repeating entries. In other words, it happens because you have many entries which don't benefit from a checkbox filter. They leave the "Text Filters >" so you can search, but leave out the checkboxes because it would simply be too long of a list. I don't know of a way to change this behaviour as it's built into Access as a non-customizable.

Bottom line: Too many options for the checkbox dialogue, no way to change it.

Underexposure

You can change the max number of displayed options from the database options, see the answer https://mcmap.net/q/980683/-quick-filter-in-datasheet-is-missing . – Chartography

Answer 8

The datatype on the column looks to be number in which case it doesn't have text filters at all. Notice your options for filtering are "Number filter" instead of "Text filter"

Waisted

Not quite; even Number fields have this sort of Checkbox filter, but again, only for a low number of different numbers. I don't know the exact limit. – Underexposure

ah, you are right. I guess I have tended to not notice it on columns with a low variety of numbers. – Waisted

It's not really that common. However these checkboxes are really only useful for small sets of data anyways, otherwise if there is a lot of searching etc going on, you should create your own filters as needed. – Underexposure

Answer 9

I solved a similar problem -- quick filters not available in datasheet part of a split form in Access 2013. The form property setting for Shortcut Menu had been set to No. I changed it to Yes and, voila, the quick filters became available.

Sousa

Answer 10

Simply change your data type to short text and it will work.

Rosewood

Answer 11

Make sure you don't use "NO" as a field name. I had field called NO and it caused the filter to dissapear. It seems to be undocumented "feature" of MS access.

Gigolo

Answer 12

Here, I find the result. First, I want to thank this answer, he gives me the spark.

"The form's data source query had a filter parameter set which was causing recursive filtering. When I remove that filter ( as it was of no use) it worked well. It was like a logical error which created that issue."

I use two code to show the solution, actually it's caused by Access's own reason.

Code1:

Me.RecordSource = "SELECT * FROM CAPData ORDER BY [DAILY_CLIENT],[CLIENT_NAME],[ASSET_NAME]"

In this case, sometimes it will cause no auto filter.

Code2:

Me.RecordSource = "SELECT * FROM CAPData Where Not IsNull(ID) ORDER BY [DAILY_CLIENT],[CLIENT_NAME],[ASSET_NAME]"

In this way, problem fixed.

Reason: Access will set auto filter when you add some condition in the recordsource SQL. if you haven't set that, when the record is larger than some number, the auto filter won't appear. But when you add a where condition in the recordsource SQL, you can find auto filter back. Have tested under 20000+ records.

第一次写回答，好激动。

Crackpot

Answer 13

There are multiple reasons for the quick filter not showing, as already evidenced. The three PRIMARY reasons I've run into:

1. You have set the form's recordsource using 'custom' SQL in VBA or in the form's property window -- these will prevent Access from having the 'quick filter' column choices.

To solve, you'll want to dynamically push your SQL into an Access query object, then set the recordsource of the form to the query - problem solved.

I use a module I wrote called CreateQuery to do that and call it like so:

E.g. Form1.recordsource = CreateQuery("SELECT * from Table", "qrySource1") using the sub below:

PUBLIC SUB CreateQuery(SQL as String, qryName as String)
   Dim qDef As DAO.QueryDef
   With CurrentDb
    For each qDef in .QueryDefs
     If qDef.Name = qryName Then
         .QueryDefs.Delete (qryName)
         Exit For
     End IF
    Next qDef

   set qDef = .createQueryDef(qryName, SQL)
END WITH
set qDef = nothing
END SUB

2. The next one is really sneaky - Check to make sure the source table's field isn't Long Text. Access will not allow a column 'quick filter' on any field that is long text.

3. Make sure you haven't disabled the Shortcut Menu option for the form, which is found in the "Other" tab of the Form properties.

Kutzer

Answer 14

I had an issue where a field wasn't 'Sortable', ie the A-Z option wasn't showing when right clicking on a column in Datasheet view. I removed the field from the design view, Control Source and re-selected it and the sorting was then available.

Leighton

Answer 15

File->Options->Current Database-> Scroll down to the section -> Filter lookup options for myDBName Database Check the ODBC fields check box

Felsite

Answer 16

As I have not seen my problem listed among the answers, I'll add it here:

In my particular case, the underlying control that I showed and which I could not filter, did not have a direct record source. Rather I showed a calculated answer that was based on two other fields.

To resolve the problem, I moved the calculation form being performed in the user form control, to the source query. Once done, the dropdown field allowed me to sort and filter again as expected.

Zsa

Answer 17

There are many different causes for this issue, most of the answers posted by others are valid, but as there are multiple causes for the error there are multiple different solutions, the most comprehensive answer was by Jerm Smith https://mcmap.net/q/980683/-quick-filter-in-datasheet-is-missing as it sets out a clear structured approach to locate the one you need to fix. I have encountered this problem many times and each time had a different cause and solution, Below are some additional steps to help resolve.

1. Rebuild Indexes: Removing and re-entering the table indexes has worked for me in the past.

2. Column Names: If any of your fields have spaces in the column name, this will stop the quick sort, ensure you use a field alias that doesn't include spaces.

3. Column Names starting with a number: Do not use a number at the start of a column name.

Alberta

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Storage

Hi Admin, I'm endorsing the correct answer posted by Jerm Smith based on my experience with this type of error. Many of the answers posted are valid in certain cases as there are multiple causes, Jerm Smith's answer addresses multiple causes and is the most comprehensive answer that should be followed first to resolve the issues. Based on my own experience – Alberta

Answer 18

Siimply change the record source in form design from : "SELECT * FROM [ABC];" to ABC. It will work...

Sorkin

Answer 19

I have found that if the record source for the form is an SQL statement including a sort clause the quick filters do not appear.

Solutions: either remove the sort clause (not that helpful if you want the data sorted) or save the SQL statement including the sort as a query, then change the record source of the form to the saved query. Voila Quick filters are then available.

Regards Peter.

Katy

Peter, this question is too old and now I don't have environment to simulate issue, so sorry cannot check your solution. Thank you for posting your solution. – Politesse

Nope. It does not make the differece – Arsenate