'Quick filter in datasheet is missing

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?

ms-access

Solution 1:^[1]

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.

Go to "Options"
Find "Current Database"
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.

Solution 2:^[2]

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

Solution 3:^[3]

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.

Solution 4:^[4]

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.

Solution 5:^[5]

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.

Solution 6:^[6]

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"

Solution 7:^[7]

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.

Solution 8:^[8]

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

Solution 9:^[9]

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.

Solution 10:^[10]

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.

???????????

Solution 11:^[11]

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:

You can't use alias for the ID column.
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)

Solution 12:^[12]

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

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

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.
Make sure you haven't disabled the Shortcut Menu option for the form, which is found in the "Other" tab of the Form properties.

Solution 13:^[13]

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.

Solution 14:^[14]

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

Solution 15:^[15]

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.

Solution 16:^[16]

Yes the answer posted by Jerm Smith https://stackoverflow.com/a/66994519/13357856 is the correct one. Note: as there are multiple causes for the quick sort to disappear, you need to try several of the solutions before you find the one that resolves your issue, if the first one doesn't work ensure to try all of them.

Rebuild Indexes: Removing and re-entering the table indexes has worked for me in the past.
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.

Also avoid a column name that starts with a number.

Solution 17:^[17]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	DFoxII
Solution 3	Peter Mortensen
Solution 4	Vlado
Solution 5	StuckAtWork
Solution 6	Brad
Solution 7	mmoershel
Solution 8	A.L
Solution 9	user3765030
Solution 10	Kevin Ye
Solution 11	Peter Mortensen
Solution 12	Jerm Smith
Solution 13	Rae G
Solution 14	user18207689
Solution 15	rohrl77
Solution 16
Solution 17	Peter

'Quick filter in datasheet is missing

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]

Solution 16:[16]

Solution 17:[17]