Sort Not Working
By Richard Rost
39 days ago
Sort & Filter Not Working in Your MS Access Form?
In this Microsoft Access tutorial, we will talk about why sorting or filtering might not work on calculated fields in your forms and what you can do to fix it. Using an example where we calculate the length of a text field, I will show you how to create a calculated field in a query and rebind your form to that query so that sorting and filtering become available. We will also discuss the difference between calculations in form controls versus queries and why that matters for sorting and filtering your data.
Dominic from Minneapolis, Minnesota (a Platinum Member) asks: I run a trophy engraving business and keep all the text in an Access database. I wanted to make a field that shows how long each engraving message is so I can flag the ones that are too long to fit on a plate. But I can't seem to sort or filter the list by that number.
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Keywords
TechHelp Access, sort or filter calculated field in forms, calculated field not sortable, query calculated field, form recordset, Length function, LenLastName, continuous form, record source, bind form to query, filter calculated values, customer list form, field not in recordset, move calculation to query
Transcript
Today we have one for the beginners. We are going to talk about why you might not be able to sort or filter based on a specific field in your Microsoft Access forms. I get this one all the time. I made a form. Whether it is a single form or a continuous form and I have a calculated field on it, I want to be able to sort based on that field or filter based on it, but I right-click, and the menu does not show those options. What is the problem? We are going to talk about that today.
Today's question comes from Dominic in Minneapolis, Minnesota, one of my Platinum members. Dominic says, I own a trophy engraving business and keep all the text in an Access database. I want to make a field that shows how long each engraving message is so I can flag the ones that are too long to fit on a plate. I cannot seem to sort or filter the list by that number.
Dominic sent me some images of his database, which I am not going to share because he asked me not to, but I am going to show you what he did and then I am going to show you how to fix it.
Before we get started, a couple of prerequisites. Obviously, if you have not watched my Access Beginner 1 class, go watch it. It is free. It is on my YouTube channel. It is on my website. It will teach you all the basics.
Today, we are going to be working with a continuous form. It is a form where you have a bunch of records on the screen at the same time as opposed to a single form, which only has one record. You should know how to create calculated fields in forms and queries. Go watch this video if you do not. Today, we are going to be using the Length function, which is how you figure out how long a particular string is. If you are not familiar with that, go watch my string functions video. Again, these are all free. They are on my YouTube channel and my website. Go watch them and then come on back.
There I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want. In this database, we have a customer list form that is a continuous form that has all my customers in it. Now, what Dominic wants to do is take one of these fields - for him, it is an engravement text (what is going on the trophy) - and he wants to see how long it is right here.
So he created a calculated field in the form. I am just going to get rid of these other guys here. Let me just delete these ones here. Select and delete. We are going to call this one here the Length. Okay. How long? Let's do last name. Let me get rid of this some calculation there too.
Let's open up this guy here. It is currently bound to the State field, so we are going to change that. I am going to call this here LenLastName, just like that. Here I will zoom in so you can see it better. Shift F2. That got really big. Let me bring it down here.
LenLastName is the name of the field. That is the name of this box now. I do not want to bind it to State anymore. I want to bind it equal to the length, using the Length function, of LastName like that. Again, I will zoom in so you can see that better.
Oops. Someone is beaming in. There it is. Length Last Name. Access is being helpful by putting those brackets around it, but you do not really need those. It is not going to hurt anything, but since I do not have spaces in my field names, I do not need those brackets. Access puts them on there. You can ignore them.
Save that. Close it. Close it. Open it. There we go. There is the length of that last name field. That is perfect.
Now, here is the problem though. You want to see the big ones up top. Right-click. Eh. Eh. What is going on here? Why can not I? I do not get the filter sort. If I click here, right-click. Oh, there is a sort. Sort A to Z. Right-click. Sort Z to A. I have all my filters. But right here, I am not getting them. Why do not I get them for the Length?
Well, the problem is when you calculate a value directly in a form control, like we just did, Access only knows that value for the one record currently being displayed. Each record that you are on, it knows that value, but it does not know the rest of them, because that value does not exist in the form's recordset.
What is a recordset? The recordset is the set of records underneath the form. In this case, it is all of the records from the customer table, but you can have different recordsets, for example, ones that are based on queries, and the query can do extra things. It can sort the recordset, it can filter the recordset, it can do calculated values for the recordset.
Sorting and filtering only work on fields that come from the form's underlying data source - the recordset, the table, or query directly. Once you move that calculation into the recordset, then Access can evaluate it for all of the records and sorting and filtering will then work.
So the solution is we need to make a query. Create a query in design, and bring in the customer table. Where are you... right there. Customer table. I usually shut this thing off. Bring in the fields that you want for your form. We need CustomerID, FirstName, and LastName, and now we are going to make that calculated value over here. I am going to zoom in again so you can see it better. Shift F2. We are going to call it LenLastName: Length([LastName]). The calculated field is a colon and the Length of LastName, which is basically what we had in that form field, but now we are creating a calculated field in the query for it.
Run it real quick and you will see you have the same results. Save it. Let's call it CustomerListQ. I do not think we have that already, because we are going to use this for the customer list form.
Now what we have to do is go to the customer list form. The first thing we have to do is bind this form to that query, because right now if you open up the form's properties by double-clicking right there on the little box, if you go to the Record Source, it is CustomerT. So it is still getting its data from the table. We are going to change this now to that CustomerListQ that we just made.
Now it is getting its data from the query, but we also have to change this guy because we are no longer going to put the calculation in here. Delete that. Now we can drop this down and pick from the LenLastName field that we created in the query. This has just got a list of fields in that query.
Save it. Close it. Open it. Looks exactly the same. But watch this. Right-click. There we go. Look at that. Sort Largest to Smallest. There are the big ones on top. You can right-click and filter equal seven. Because now the underlying recordset has that field in it. It has that data in it. So the form can see all of them, not just the one that you are on.
The form is not doing the work. The query is doing the work. That is how you fix that problem.
Now, this is just one reason that you might have problems where you might not be able to filter and sort based on a calculated value in a form. There are a bunch more. I might cover them in other videos, but this is 90 percent of the time when someone says, hey, I have a form and I cannot sort, I cannot filter. That is why - because you have a calculated value in the form. You just simply need to move that to an underlying query.
We do not store calculations in our tables. Even though you can, you should not. 99 percent of the time you should not. There are some reasons why you might want to, but that is a lot more advanced.
There you go. There is your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you next time.
Quiz
Q1. Why might you not be able to sort or filter on a calculated field in a Microsoft Access form?
A. The calculated value only exists in the form control and not in the form's recordset
B. Sorting and filtering are disabled in continuous forms by default
C. Calculated fields require special permissions to be sorted
D. Access does not support sorting or filtering at all
Q2. What is a form's "recordset" in Microsoft Access?
A. The page layout for displaying data
B. The set of records provided by the form's underlying table or query
C. The visual controls used on the form
D. A backup copy of all records in a table
Q3. In the example from the video, what function was used to calculate the length of an engraving message?
A. SUM
B. LEFT
C. Length
D. MID
Q4. What is the recommended way to make a calculated value, like the length of a field, sortable and filterable on a form?
A. Add the calculation directly in the form's text box control
B. Store the calculation as a physical field in the table
C. Move the calculation into a query that serves as the record source for the form
D. Use an Excel spreadsheet linked to the form
Q5. What happens when you calculate a value inside a form control but do not include it in the recordset?
A. The value is available for all records and can be sorted and filtered
B. Filtering and sorting on that value are not possible because Access only knows the value for the current record
C. Access automatically creates a query in the background
D. The value is stored permanently in the table
Q6. According to the video, why is it generally NOT recommended to store calculations in your tables?
A. Calculations take up more space and slow tables down
B. Calculated fields cannot be used in queries
C. Storing calculations in tables can lead to data inconsistency and redundancy
D. Tables are unable to display calculation results
Q7. After moving the calculated field to the underlying query and binding the form to that query, what changes for the user?
A. The form looks different but sorting and filtering still do not work
B. The calculated field disappears from the form
C. Sorting and filtering by the calculated field now works as desired
D. The form requires special permissions to function
Q8. When creating a calculated field in a query, what is the correct syntax?
A. Length = LastName
B. LenLastName: Length([LastName])
C. [LastName] Length
D. LastName: [Length]
Q9. What type of form was Dominic working with in his trophy engraving business example?
A. Single form
B. Continuous form
C. Split form
D. Datasheet form
Q10. Why does Access sometimes put brackets around field names in expressions?
A. Brackets are always required for all field names
B. Brackets are necessary only for numeric fields
C. Brackets are used when field names contain spaces or special characters
D. Brackets make queries run faster
Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-B; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone addresses a common question for beginners working with Microsoft Access: why you sometimes cannot sort or filter based on specific fields in forms, especially when dealing with calculated fields.
I am often asked by students why their calculated fields in a form cannot be sorted or filtered. For example, you might add a field to show the length of a text entry in your continuous or single form using a calculation, but when you try to right-click to sort or filter by that field, the options are missing. Today we will look at why this happens and how to resolve it.
Let me set the stage with a scenario. Suppose you run a business where you engrave plaques and keep all the text in an Access database. You want an easy way to see how long each engraving message is, so you can ensure it fits on the plate. You add a calculated control to your form to count the characters. Everything looks fine at first, but you quickly notice you cannot sort or filter by this new calculated value.
Before we move into the solution, make sure you are comfortable with the basics of Access. If you have not yet watched my Access Beginner 1 class, it is available free on my website and YouTube channel and covers the foundational material. You should also know how to create calculated fields in both forms and queries, and be familiar with using functions like Length to work with text.
In our tutorial, I will use a sample database called the TechHelp free template. Within this database, there is a customer list form designed to display multiple records in a continuous format. This setup is common when you want to see several records on the same screen.
Suppose you want to display, right on the form, how long each customer's last name is. You add a text box (let's call it LenLastName) and set its control source to calculate the length of the last name using the Length function. This control is now showing the desired values for each visible record.
However, here is the stumbling block: if you right-click this field, there is no option to sort or filter. You might check a different field, such as LastName, and notice sorting and filtering are available there. Why does this calculated field behave differently?
The issue stems from where the calculation is happening. When you put a calculation directly into a form control, Access only knows the value for the current record being displayed. This value does not exist as part of the form's recordset - in other words, the underlying set of data that the form works with, which is either a table or a query.
In Access, sorting and filtering only function on fields that are in the form's record source – the recordset itself. Calculated controls on the form are not part of the recordset, so Access cannot evaluate or sort all the values at once.
The solution is to move your calculation from the form control into the form's record source, typically a query. Here is how you do that in general terms. You start by creating a new query in design view. Add the relevant table, such as CustomerT. Bring in every field you need from your base table, for example CustomerID, FirstName, and LastName. Then, add a new calculated field in the query grid to compute the length of the last name. You give it an alias, such as LenLastName, and use the Length function to calculate it.
After you save and run the query, you verify that your calculated field is working as intended, showing the length for each record. With the query in place, change the data source of your form: open your form's properties and set the Record Source to your new query instead of the table.
Finally, adjust the text box on your form. Instead of performing the calculation right there, bind the text box to the LenLastName field you created in the query. Now the field is part of the recordset that the form is based on.
When you open the form, everything will appear the same to your end users, but now when you right-click the calculated field, sorting and filtering options are available. Access can now analyze all values because the calculated field exists in the recordset, not just as an isolated calculation in the form.
To sum up, for calculated values you want to sort or filter, always put those calculations in a query, not directly on the form. This is a fundamental best practice in Access. There are other reasons you might not be able to filter or sort calculated values in forms, but this is by far the most common cause and solution.
Remember, you should generally avoid storing calculated results in your tables. While it is technically possible in Access, it is rarely necessary and can lead to problems later on.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Understanding sorting and filtering limitations in Access forms
Using calculated fields in Access continuous forms
Creating a calculated field using the Length function
Why sorting and filtering do not work on form-calculated controls
Explaining the concept of a form's recordset in Access
Creating a calculated field in a query rather than in the form
Creating and saving a new query for use in a form
Binding a form to a query instead of a table
Adding a calculated query field to a form control
Enabling sort and filter on calculated fields via a query
Summary of why calculations should not be stored in tables
Article
If you are working with Microsoft Access forms and find yourself unable to sort or filter based on a specific field, you are not alone. This issue often comes up, especially for beginners. Imagine you have a continuous form that displays a list of records, such as customer information, and you add a calculated field like the length of a text field. You might expect to be able to sort or filter by this calculated value, but when you try, the right-click menu does not show the sort or filter options for that field. Let me explain why this happens and how to fix it.
Let's use a common scenario as an example. Suppose you run a trophy engraving business and want to check how long each engraving message is, so you can spot any that might be too long to fit on a plate. You create a form that lists all engraving messages and add a textbox to calculate the length of each message using the Len function. This seems like a straightforward solution, but when you try to sort or filter the form by this new field, the options are missing.
The root of the issue is how Access handles calculated fields in forms. When you add a calculation directly to a control (like a textbox) on a form, Access only evaluates that calculation for the current record being displayed. That calculated value is not part of the form's recordset – the set of records that comes from the table or query underlying the form. Sorting and filtering in Access require the field you are interested in to be part of that recordset. If your calculation lives only in a textbox on the form, Access cannot use it as a basis for sorting or filtering the entire list, because it does not exist for every record in the recordset at once.
So, what is the solution? You need to move your calculation from the form control into the query that serves as the form's record source. By adding the calculated field to the query, the result is available for every record in the recordset. Access can then sort and filter on this field just like any other.
Here's how to do it step by step:
First, open the query designer and add the table that holds your data – for example, the Customers table. Add the fields you want to appear on your form, such as CustomerID, FirstName, and LastName. Now, in a new column of the query grid, create your calculated field. For example, to calculate the length of the LastName field, you would enter:
LenLastName: Len([LastName])
This creates a new field called LenLastName, where Access computes the length of the LastName value for every record in the query's result. When you run the query, you will see this new field along with your regular data.
Once you have your query, save it with a name, for example, CustomerListQ. Now, open your form in Design View and open its properties. Look for the Record Source property, and change it from your table (such as Customers) to the new query (CustomerListQ).
Next, update the control that displayed your calculated value before. Instead of having a formula like =Len([LastName]) in the control's Control Source property, set it to the new field from your query, LenLastName. You can do this by choosing LenLastName from the control's Control Source dropdown.
Save and close the form. When you reopen it, the calculated values will look the same as before, but now they come directly from the query. When you right-click on the LenLastName field in the form, you'll see the full range of sort and filter options, because Access can now use that field for all records in the underlying recordset. You can sort by length, filter to see only values above a certain size, and more.
In summary, if you are unable to sort or filter by a calculated value in an Access form, the calculation is likely in a control rather than in the form's underlying recordset. Moving that calculation to the query solves the problem. This approach maintains good database design: we avoid storing calculated values directly in tables, and keep calculated fields in queries where Access can use them for sorting and filtering.
This simple change saves a lot of frustration and helps make your Access forms much more powerful and flexible. Try it out next time you hit this roadblock!
|
