|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Welcome Welcome! Advanced Search: Sort & Filter Welcome to Microsoft Access Developer Level 26. In this course we will start with a bug fix from Developer 25 related to inventory calculations and product groups. Then, we will build two types of advanced search forms for customers: the first with custom sorting and filter boxes, and the second using a criteria table and recordsets for more flexible searches. We will also add features like clickable column headers for sorting, dynamic filter boxes, AND/OR search conditions, and storing search criteria in a table with options based on field data types. Access Developer Level 25 is a recommended prerequisite. NavigationKeywordsAccess Developer, search form, advanced search, criteria table, custom sorting, filter boxes, customer list box, dynamic WHERE conditions, recordset, sort null values, AND OR search, field filter, combo box fields, inventory calculations, save search crit
More InformationTranscriptWelcome to Microsoft Access Developer Level 26 brought to you by AccessLearningZone.com. I am your instructor Richard Rost. In today's class, we are going to start off with a bug fix that one of my students discovered from Developer 25. Then, we are going to build two different types of search forms. The first one is a simpler form that has some custom sorting on it and some custom filter boxes. Then the second search form is going to be that one on steroids. We are actually going to make a criteria table where you can put all kinds of criteria in a separate table and then use that table with a recordset to filter the original list of customers. Some really cool stuff in this lesson. This class, of course, follows Access Developer Level 25. I strongly recommend you take that class first before this one. It is also recommended that you have taken all my Beginner, Expert, Advanced, and other Developer classes too. I am using Microsoft Access 365, part of a 365 subscription. It is roughly equivalent to Access 2019. 2016 will work just fine too. In fact, the lessons covered in this class should go all the way back to about 2007 without any problems. But do not quote me. I only support 2016 and 2019 at this level. Got questions about the material covered in this lesson? Just scroll down to the bottom of the page and post them right there in the questions box. As always, if you have any questions about stuff about Access that is not related to the material in this class, go ahead and post it in the Access Forum. Let's take a closer look at what is covered in today's class. In lesson one, we are going to start off with a bug fix, something that was discovered in Developer 25. We are going to have to make inventory calculations ignore product groups when we go to ship an order. In lesson two, we are going to begin building an advanced search form to search through our customers. We are going to start off building a basic, unbound form with a customer list box on it. The first thing we are going to do is work on sorting so we can click on any of these labels here in the column headers to sort based on a field. We are going to sort the null values at the bottom, which is different from how Access normally treats them. It normally sorts null values at the top, which I do not like. We will make it so it colorizes whichever one of these columns is the sort field. Then we will reverse that from blue to red when we sort descending. Lots of cool stuff in this lesson. In lesson three, we are continuing on with our search form. We are going to put field filter boxes across the top of each column so you can type in a letter or a string and it will filter based on whatever you type in. We will put a clear button at the end so you can clear those filters. We will build dynamic WHERE conditions into our SQL, so we can say use this field and or this field and or this field, and we will use an AND/OR drop-down box. You can pick an AND or OR condition for your search. Lots of cool stuff in this lesson too. In lesson four, we are continuing on with our search form and doing something completely and totally brand new. We are going to make a table and the table will store the search criteria. We are going to loop through the records in that table to create the WHERE condition for the search form using, guess what, a recordset. You did not think you were getting out of Developer 26 without at least one recordset, did you? Lots of brand new stuff in lesson four. Here we go. In lesson five, we are continuing on with the search form. We are going to loop through all the fields in the customer table and fill them into a combo box so we can pick from any field in the list. Then, based on the data type of that field, if it is text we will allow the LIKE condition to be possible. If not, it will just be the equality stuff. Then put the criteria in, add the criteria, save it in the table, refresh the main list, and then we will make a delete button. Lots of cool stuff in this one, too. IntroWelcome to Microsoft Access Developer Level 26. In this course we will start with a bug fix from Developer 25 related to inventory calculations and product groups. Then, we will build two types of advanced search forms for customers: the first with custom sorting and filter boxes, and the second using a criteria table and recordsets for more flexible searches. We will also add features like clickable column headers for sorting, dynamic filter boxes, AND/OR search conditions, and storing search criteria in a table with options based on field data types. Access Developer Level 25 is a recommended prerequisite. QuizQ1. What is the first topic covered in Microsoft Access Developer Level 26? A. Bug fix from Developer 25 B. Building a navigation menu C. Creating a report layout D. Automating email notifications Q2. What is unique about the second search form built in this class? A. It uses only a combo box for selection B. It utilizes a criteria table for filtering C. It sends the search results directly to Excel D. It only allows searching by customer ID Q3. What is the primary advantage of the advanced search form discussed in lesson two? A. It can generate summary reports automatically B. It supports custom sorting and filter boxes C. It only works with numeric fields D. It operates in table view only Q4. How does Richard customize the sort order for null values in the customer search form? A. Null values are sorted randomly B. Null values are sorted at the top C. Null values are sorted at the bottom D. Null values are hidden from the results Q5. What feature is added to indicate which column is currently sorted? A. The field label turns green B. The field label is underlined C. The field label becomes italicized D. The field label changes color Q6. What functionality do the field filter boxes provide in lesson three? A. They allow users to update customer records B. They filter the list based on user input for each column C. They print filtered results D. They send data to an external database Q7. How does the clear button on the filter row work? A. It deletes all customers in the list B. It exports the filtered list to Excel C. It removes all applied filters and resets the list D. It saves the current filter as a template Q8. What is a major new concept introduced in lesson four? A. Printing invoices automatically B. Storing search criteria in a separate table C. Sending data to Power BI D. Creating a split form for customers Q9. How are WHERE conditions dynamically built in lesson four? A. Manually entering SQL code on a form B. Using macros to concatenate filters C. Looping through a criteria table with a recordset D. Using static queries stored in Access Q10. What is the purpose of filling a combo box with all fields in the customer table in lesson five? A. To allow selecting multiple records at once B. To let users pick any field to set search criteria on C. To change the data type for fields D. To assign user permissions Q11. When the selected field in the combo box is of text type, which search condition can be enabled? A. SUM B. AVG C. LIKE D. COUNT Q12. What does the delete button created in lesson five do? A. Deletes all customers from the table B. Deletes the currently highlighted filter criteria C. Deletes the entire customer table D. Deletes all forms from the database Answers: 1-A; 2-B; 3-B; 4-C; 5-D; 6-B; 7-C; 8-B; 9-C; 10-B; 11-C; 12-B 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. SummaryToday's video from Access Learning Zone is Developer Level 26. I'm Richard Rost, and in this lesson, we're building on the material from Developer Level 25. I highly recommend that you complete all of my Beginner, Expert, Advanced, and earlier Developer courses before moving on with this one. I'm using Microsoft Access 365, which is part of the Office 365 subscription, but the concepts here apply to Access 2016 and 2019 as well, and even back to Access 2007 for most topics. Just keep in mind that I only support Access 2016 and 2019 at the Developer level. Here's a rundown of what you'll learn in this class. We start off in lesson one by addressing a bug that a student found in Developer 25. Specifically, we're going to correct an issue with inventory calculations by making sure that product groups are ignored when it's time to ship an order. This will ensure that only the appropriate products affect your inventory calculations. In lesson two, we begin creating an advanced customer search form. We'll set up a basic, unbound form with a list box containing your customers. Our first task will be to add sorting functionality so that you can sort the list by clicking on the column headers. By default, Access places null values at the top, but we'll change this to put nulls at the bottom, which I find more practical. The sorted column will be highlighted, and its color will change to indicate whether it is being sorted in ascending or descending order. Lesson three continues our work on the search form. We'll add filter boxes above each column, allowing you to type in a value to filter the list dynamically. There will also be a clear button so you can remove all filters at once. We'll create dynamic WHERE conditions in our SQL that let you decide whether to use an AND or OR logic for your search conditions, controlled by a dropdown box. This adds a lot of flexibility to your searches. In lesson four, we'll take the search form even further by introducing a brand new concept. We'll build a criteria table where your search requirements are stored. Then, we'll use a recordset to loop through these records and build a dynamic WHERE clause for the search. This is a more advanced approach and opens the door to very powerful searches. Lesson five wraps up the search form project. Here, we'll loop through all of the fields in the customer table and populate a combo box, so you can pick any field to filter by. Depending on the field's data type, the form will allow different search conditions. For example, if it's a text field, you'll be able to use LIKE searches. Otherwise, we'll stick to equals and similar operators. You'll be able to enter criteria, add it to the criteria table, and refresh the main list. We'll also add a delete button to remove unwanted criteria. Everything I've described here is explained in detail in my full video tutorial, where you'll see step-by-step instructions for each task. You can find the complete lesson on my website at the link below. Live long and prosper, my friends. Topic ListBug fix for inventory calculations from Developer 25 Building a simple customer search form Custom sorting of list box columns Handling null values in sorting Colorizing sorted columns Reversing sort color for descending order Adding filter boxes to search form columns Clearing filters with a button Dynamic WHERE conditions in SQL Using AND/OR drop-down for search conditions Storing search criteria in a separate table Looping through criteria table with a recordset Filling search field combo box from customer table fields Adjusting search operators based on data type Saving and deleting search criteria Refreshing customer list after filter changes Primary Topicsbug fix in inventory calculations, advanced search form creation, dynamic SQL WHERE conditions, custom sorting, recordset-based filtering, criteria tables, field filter boxes Secondary TopicsUI enhancements for search forms, colorized sorting indicators |
||
|
| |||
| Keywords: Access Developer, search form, advanced search, criteria table, custom sorting, filter boxes, customer list box, dynamic WHERE conditions, recordset, sort null values, AND OR search, field filter, combo box fields, inventory calculations, save search crit PermaLink How To Build Advanced Search Forms With Custom Sorting and Filters in Microsoft Access |
