Access Search Seminar
Searching and Sorting in Microsoft Access
Welcome
This seminar will teach you just about everything there is to know about searching and sorting data in Microsoft Access. This seminar is for the intermediate to advanced Access developer.
You will learn how to perform simple search and sort operations in Access. You will understand complex query parameters (especially when it comes to working with NULL values). You will gain a basic understanding of SQL, specifically SELECT queries. Finally, you will learn how to create dynamic SQL statements to control form searches and sorts with VBA commands.
 Resources
Main Seminar Goals
- Basic find & replace, filter & sort operations
- Create parameter queries to prompt users for input
- Use form fields as parameters without prompts
- Perform wildcard searches
- Learn how to deal with blank or NULL data
- Search for records using a combo box on a form
- Difference between embedded macros and VBA
- Design a single-field search button
- Learn how to apply filters using VBA commands
- Design search forms using custom SQL
- Manipulate your form search results and modify the sort order of records
- Create a simple search form designed for novice users with hidden advanced options
- Build a recent customer list to quickly open those clients you work with often
- Search for data in multiple tables using one quick search form
- Search for products using ranged criteria (between dates, dollar amounts, etc.)
- Quickly view summary information on returned result sets (count, max, min, etc.)
- Design a natural language search that can decide what type of search you want
- Create a search-as-you-type form that updates results with each keystroke
Topics Covered
We're going to start out in the first couple of lessons going over basic find, replace, filter, and sort techniques. Even if you consider yourself and advanced access user you should watch these first couple of lessons. You might pick up a couple of tips and tricks you didn't know and some techniques for showing other people how to perform basic searches inside of your Access databases.
Were going to then learn how to work with parameter queries which is having the database ask us for parameter for some criteria. We'll learn how to work with multiple AND/OR conditions and how to use those parameter queries in our forms.
We'll set up some database infrastructure including a Main Menu form, a continuous form to show all customers which will then filter to the show only customers in a particular state. Then we will make a button to open the specific selected customer from that continuous form.
We will learn how to work with form field parameters which is setting a value on one form and then using that as a criteria when we open up another form.
You will then learn about wildcard searches which is specifying a portion of a criteria and showing all the records that "kind of" match. For example, showing all states that start with the letter "N." We will then will cover a very difficult topic for beginner Access developers: how to deal with blank values. If the user leaves a criteria blank, do you want to show the NULL values or not.
Next you'll learn how to make a search combo box. We can simply pick a customer from a list and have the form jump immediately to a specific customer record.
Next we will learn about embedded macros and how they're different from VBA functions. We'll talk about the pros and cons of working with each. Then we will make a single field search button. This will be a button that searches a particular field, such as company name or phone number. Click the button, type in your criteria, and Access finds the first record matching your criteria. We'll design this form first using embedded macros, and then using VBA commands.
Next we will cover multiple field filters. Using some VBA code we can specify multiple parameters in a continuous form. Specify an AND/OR condition, click the Filter button, and Access will show you just those records.
Then we'll build something similar to the previous example, but instead of using filters will use SQL record source changes. Once again we'll see how to deal with blank values or NULL values in our search results.
Next we will learn how to change the sort order of our search results. Would you like ascending or descending sort? We'll change that with some toggle buttons. We'll also learn how to sort based on multiple fields.
We will take a look at how to build a search form that is designed for the novice user. If you have other people that are going to be using your database that aren't experts in Access, you want a way for them to quickly and easily search for information in the database. So we'll build a nice simple search form for them.
However, for you and other advanced users, you want to be able to enter additional search options. I'll show you how to make a button to resize your form show the advanced options menu. We'll learn how to resize a form using VBA code.
If you're like me, you probably have a small group of customers that you deal with on a regular basis. Next you'll learn how to create a recent customer list as part of your search form. Whenever you have a customer that you know you work with often, you can add him to a little mini list of recent customers. Just double-click on that customer's name his record opens up.
Next we will make a multi-table search form. If you're looking for information and you're not sure which table the data is in, you can type information to this form and it will search whichever tables you specify. For example you may wish to find all the people in your database from New York, whether they are employees, vendors, or customers. This form will search all three tables and present you with one single list. Double-click on any item to open the particular form that data is found on.
We we'll then build a form for searching products in a product table. You will learn how to search by vendor or product name but more importantly between ranges of values. For example, you can find all products that were updated between two dates, or find all products that are between a range of unit costs. You might have a particular product and you want find out who the cheapest vendor is. You'll learn how to display summary information on your search results such as the count of items found, the maximum value, the minimum value, and so on.
Next we will build a form focusing and natural language searching. This is where Access tries to determine what you're looking for based on the phrasing of your search terms. For example, are you searching with multiple keywords, or are you looking for a phrase inside of quotes. The search form will actually write custom SQL code based on how you type in your search phrase. This search is performed in as many fields of as many tables that you like.
We will then customize the natural language search form and turn it into a search-as-you-type form. As you are typing in your search phrase, Access updates the results with each character you press. You'll find this is very similar to how Google gives you the instant search results.
Finally we will learn how to take the custom SQL that our forms are writing for us and use that to print a custom report.
Lesson Summary
Lesson 1: Review the sample database that you can download for this class and we'll see how to perform a simple find and replace operation.
Lesson 2: Learn how to apply simple filters and sorts to our tables and forms.
Lesson 3: Begin building a customer search form by using a parameter query to prompt the user for bit of information and then display the search form with just that data.
Lesson 4: Learn about menus and buttons. Make a main menu that will open up when the database starts. We will make a button to browse customers, and another button to show customers by state, and then open a specific customer record.
Lesson 5: Learn how to get a parameter from a form field instead of having to use pop-up prompts.
Lesson 6: Wildcard searches. We can search for records based on any part of a criteria.
Lesson 7: Learn how to work with blank or NULL values.
Lesson 8: Use the combo box wizard to create two different combo boxes to search for records in our form. Search for company name or customer name.
Lesson 9: Learn about Embedded Macros, how they're different from Visual Basic code, and the pros and cons of each.
Lesson 10: Build a single field search button using a Macro.
Lesson 11: Build a single field search button using VBA code.
Lesson 12: Create more find buttons. Make a find button for the company name field and for the address field. Also show you how to make your own subroutines.
Lesson 13: Apply filters using VBA code.
Lesson 14: Modify the record source of the form using an SQL statement.
Lesson 15: Add triple-state checkboxes to our form to view values that are NULL or are NOT NULL.
Lesson 16: Add a second field to search by last name then first name, or state then city. Make a set of toggle buttons sort order (ascending or descending)
Lesson 17: Build a simple search form that is designed specifically for novice users; easy to use.
Lesson 18: Add some advanced options to the simple search form. Hide them by default and make a button to resize the form and show those advanced options for power users.
Lesson 19: Create a Recent Customers List.
Lesson 20: Handle the problem of the user typing to add the same customer to the Recent Customer List that's already on the list.
Lesson 21: Dynamically resize the column widths inside of your list box and the with of a list box itself. Determing the last time and date the customer was looked up, and number of times that customer's record was opened.
Lesson 22: Add labels the top of each column and our customer search list to sort based on that column.
Lesson 23: Begin building a multi table search form we can type in some parameters and then Access will search multiple tables for those values.
Lesson 24: Set up extra list boxes for vendors and for our employees and have one search button that will search and show results for all three tables.
Lesson 25: Construct a union query to bring the results from three tables into one and add check boxes so the user can select which of those three tables they want to search in.
Lesson 26: Start working on a product search form to allow the user to work with other types of data, for example currency values, dates, times, and so on.
Lesson 27: Learn how to search between a range of unit costs, or a range of dates.
Lesson 28: Summary information on search results. When you perform a search, obtain the number of records returned, the minimum or maximum values, etc.
Lesson 29: Search for customers that purchased a specific product. Get a count of those customers.
Lesson 30: Natural language search. Search one or multiple tables, and one or multiple fields of data for an exact phrase - all from a single search box (just like Google).
Lesson 31: Learn how to break up a long search term into multiple shorter keywords
Lesson 32: Phrase searches. Negative keywords. Exact match searches.
Lesson 33: Search-as-you-type. As we key in our search phrase it refreshes the search results after each character is pressed.
Lesson 34: Learn how to take the search results we generate with their previous search forms and use that same SQL to print a report with those results
This is the perfect seminar for anyone who wants to learn how to search for or sort data in their access databases. There's something for everyone in this seminar - from beginner to advanced. Of course, if you have any questions about whether or not this seminar is for you, please feel free to contact me. I'd be happy to answer any questions you might have.
Pre-Requisites
This is a Developer-Level Seminar. There will be a lot of VBA. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video. There will be a lot of SQL in this course too. I don't assume you know any SQL before we begin however, it would be very helpful if you take my SQL Seminar, at least part 1, before taking this course. Helpful, but not required.
Version
I am using Access 2010 in this seminar, however the lessons are perfectly valid for all versions of Access going back to at least Access 2000. It is currently 2022, and I have recently made sure that everything works fine with Access 2019 and Office 365. I also made sure it works with both 32- and 64-bit versions.
Shorter Template
If you want to learn how to efficiently search and sort on your continuous forms, but you don't want to sit through nine hours of video in this Seminar, I've taken the most popular feature and created a scaled-down Access Search Template.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access search, find, replace, filter, sql, vba, parameter, wildcard, natural language, search as you type
Intro In this video, I will walk you through the key topics covered in the Microsoft Access Search Seminar, using the sample database built during the class. We will explore searching and sorting techniques in Microsoft Access, including simple filters, parameter queries, wildcard and null searches, combo box search tools, dynamic form searching, custom SQL statements, and both basic and advanced search forms. You will also see how to search across multiple tables, create product search features, and use natural language search methods. Downloadable resources and more detailed course information are available at AccessLearningZone.com.Transcript Introducing the Microsoft Access Search Seminar brought to you by AccessLearningZone.com. This 9.5-hour seminar will teach you just about everything there is to know about searching and sorting in Microsoft Access Databases.
This video will explain the concepts taught in the Search Seminar by walking you through the sample database we've built in the class. You can download a free copy of this database and get more information on this seminar by visiting AccessLearningZone.com or going to this special link: 599cd.com-xacs. There you'll find links for the sample free database, more free videos, the full course outline, and more information.
You'll learn how to perform simple searches and sorts, understand complex query parameters, gain a basic knowledge of SQL, and dynamic form searching with Visual Basic. We'll start out by covering basic find and replace operations. You'll learn how to apply simple filters and sorts, and then you'll see how to work with a parameter query to prompt the user for some input.
You'll learn about form field parameters, which means setting a value on a form and then using that as a criteria instead of a pop-up prompt. For example, search for all customers from New York and then hit the Show Customers by State button, and there they are.
You'll also learn about wildcard searches, for example, show me all customers with states beginning with the letter n, and a very difficult concept for some developers: learning how to show blank records or null values. We'll go into some pretty complex query criteria to see how to get that to work.
We'll make some simple search combo boxes where you can search for a customer and it will jump right to his or her record. We'll also make some single field search buttons where you can search just on that field, for example, last name, company name, address, or whichever field you like. I'll click on the search button for last name, type in what I'm looking for, hit OK, and Access takes me right to that record. To search for the next one, just hit the next button.
We'll create a form to search for customers based on their first name, last name, city, or state. We can enter in one parameter such as show me all customers from New York, or we can have multiple parameters, for example, show me Houston or New York, and there's both. We can easily change this to an AND condition, and now I see nobody because there is no Houston, New York in my database.
Next, we'll build a very similar form, but instead of applying filters, we'll write custom SQL statements. I can enter in my criteria here, apply a filter that then changes the SQL in the form to display a different set of records. I can have multiple criteria with an AND or OR condition. I can apply a sort based on multiple fields, for example, sort by first name. I can change from an ascending to a descending sort by clicking on this toggle button.
Using a triple-state check box, I can determine whether or not I want to see blank or null values. I can allow them, disallow them, or show both.
We'll make a simple quick search form that has multiple fields on it. This form is designed for the novice user who doesn't know a lot about Access. He or she can come in here, type in any field they want to search for. Let's say city, I'll type in Amherst (AMH) and then hit the search button. The customer form then loads up, showing the customers from Amherst.
If I have customers that I work with on a regular basis, I can add them to this recent customers list.
For advanced users, there's a button here that opens up an extra side to the search form that has some advanced options. For example, I can sort my list of recent customers by the last time they were accessed or by the number of times accessed.
Next, we'll build a form that can search inside of multiple tables. If you're looking for people and you don't know whether those people are customers, vendors, or employees, you can use this form to search inside of all three tables. For example, to find all people from New York, I'll just type in New York in a state field, hit search, and there are all the people from my employee, vendor, and customer tables that are from New York. I can select which tables to search from. Once I double-click on one of these records, it opens up the appropriate form, whether the vendor form, the employee form, or the customer form. There's the customer record.
We'll build a product search form that will allow us to search for products by vendor, product code, product ID. We'll learn how to search for products between a range of costs or a range of dates updated. For example, show me all the parts in my database that cost between 10 and 1,000 dollars. Search. There's my list of items. I can narrow that down by vendor if I want to. I'll see a count of the number of products returned, the minimum and maximum values, the number of customers that have purchased the current selected product, and the notes for that product over here. I can double-click on a product to open up the product form, or I can double-click on the number of customers to get a list of all the customers who have purchased that product. There's the one customer who purchased that product. This, of course, could be used to feed a marketing report or an email list.
Next, we'll build a form for natural language searching. For this example, I've loaded up the database with some sample course outlines from my lessons. The search form will search either the description of the course or the title of the course itself. Using one search phrase here, I can look for a course. I can look for a keyword inside of that course. I can search for an exact phrase inside of quotes. I can use negative keywords. As I'm typing, the search results are updated automatically with each keystroke. I can then use these search results to print a custom report. There's my report.
So if you want to learn how to search, sort, filter, write custom SQL, or use VBA to search inside your Access databases, the Access Search Seminar is for you. There's something in this seminar for developers of all skill levels.
To find out more information about this seminar, visit AccessLearningZone.com or go to 599cd.com/xacc.Quiz Q1. What is one of the main topics covered in the Microsoft Access Search Seminar?
A. Advanced Excel spreadsheets
B. Searching and sorting in Access databases
C. Building websites with HTML
D. Installing Access on a server
Q2. What concept is explained early in the seminar?
A. Adding new tables to a database
B. Basic find and replace operations
C. Encrypting Access files
D. Exporting data to PDF
Q3. What can parameter queries in Access do?
A. Prompt users for input to filter data
B. Automatically back up a database
C. Create new forms automatically
D. Merge multiple databases
Q4. What feature allows you to search for all customers from a particular state without a pop-up prompt?
A. Table relationships
B. Macro scripts
C. Form field parameters
D. Database splitting
Q5. How are wildcard searches useful according to the seminar?
A. To show all customer records alphabetically
B. To display only customers with completed orders
C. To find records where a field begins with a specific letter
D. To validate email addresses
Q6. What is considered a difficult concept for some developers, as mentioned in the seminar?
A. Formatting dates in reports
B. Creating lookup fields
C. Showing blank (null) records in queries
D. Importing data from Excel
Q7. What is a search combo box designed to do in the context of Access?
A. Create a new record
B. Jump directly to a selected record
C. Export a query as a PDF
D. Send automated emails
Q8. How does the seminar say you can use multiple criteria in a search form?
A. Only by writing VBA code
B. By using begin and end dates
C. By entering values and using AND or OR conditions
D. Only with simple filters
Q9. What can a triple-state checkbox be used for on a search form?
A. To filter records by date range
B. To toggle between ascending and descending sort order
C. To control showing, hiding, or allowing both null and non-null values
D. To select multiple records at once
Q10. What feature was built for novice users in the seminar's database?
A. A report designer
B. A quick search form with multiple fields
C. A data migration wizard
D. A form for advanced SQL coding only
Q11. How can advanced users sort their list of recent customers in the search form?
A. By customer email address
B. By the date the record was created
C. By last time accessed or number of times accessed
D. By alphabetical order only
Q12. What is the benefit of a form that searches inside multiple tables?
A. It can combine customer orders into invoices
B. It allows finding people who may be customers, vendors, or employees
C. It displays only products in stock
D. It generates financial reports automatically
Q13. What information does the product search form provide for each product?
A. Only the product name
B. Vendor contact details only
C. Count of products returned, min/max values, customer counts, and product notes
D. Estimated product delivery date
Q14. What type of search was demonstrated for finding courses in the sample database?
A. Image-based search
B. SQL injection search
C. Natural language search
D. Search by user permissions
Q15. What is one feature of the natural language search form in the seminar?
A. It sends email notifications for each result
B. It lets users search by keyword, exact phrase, or negative keywords
C. It only allows searching course outlines
D. It stores search results in an external database
Answers: 1-B; 2-B; 3-A; 4-C; 5-C; 6-C; 7-B; 8-C; 9-C; 10-B; 11-C; 12-B; 13-C; 14-C; 15-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.Summary Today's video from Access Learning Zone introduces the Microsoft Access Search Seminar, a comprehensive 9.5-hour course that covers everything you need to know about searching and sorting within Microsoft Access databases.
In this overview, I will explain the concepts and techniques that are taught in the seminar by using the sample database created during the class. This database is available as a free download, and you can find it along with the full course outline, additional information about the seminar, and more free video content by visiting AccessLearningZone.com or using the link 599cd.com-xacs.
Throughout the seminar, you will learn how to conduct both basic and advanced searches and sorts, work with complex query parameters, gain a foundational understanding of SQL, and set up dynamic form-based searching using Visual Basic. The course starts with essential skills such as basic find and replace functions, and then moves on to applying filters and sorts to your data. You will see how to set up parameter queries, which prompt users for input to refine their searches.
We also discuss using form fields as parameters for queries. Instead of relying on a pop-up prompt, you can allow users to set a value on a form and use it as a search criterion. For instance, you can search for all customers from New York by simply entering the state and pressing a button to display those records.
Wildcard searches are also covered, allowing you to find records with specific patterns such as all states beginning with a certain letter. You will also learn to handle the often-challenging task of finding blank or null values using more complex query criteria.
The seminar covers the creation of simple search combo boxes to quickly locate specific customer records. Single-field search buttons are another feature, enabling targeted searches on individual fields such as last name, company name, or address. With a search button for last names, you can enter the name you're looking for, press OK, and be taken directly to that record. Navigating to the next matching result is just as straightforward.
We build search forms that allow users to locate customers based on multiple criteria, such as first name, last name, city, or state. You can search using a single parameter like New York, or multiple parameters such as Houston or New York. You will also learn to toggle between using OR and AND conditions to broaden or narrow your search results.
A similar form is developed where, rather than applying filters, you can write custom SQL statements to refine your search. Inputting your criteria updates the SQL behind the form, displaying only the matching records. You can sort results on multiple fields and toggle the sort order between ascending and descending. With a triple-state checkbox, you even have the option to include or exclude blank or null values, or show both.
We create a user-friendly quick search form with multiple fields, intended for users who may not be familiar with Access. Simply type into any field, such as city, then run the search, and the form will display matching records. For your convenience, there's a recent customers list for those you interact with regularly.
For advanced users, the search form includes an option to reveal extra features. You may, for example, sort recent customers by how often or how recently they've been accessed.
The seminar also shows how to set up forms that search across multiple tables at once. This is useful if you need to find someone but do not know if they are a customer, vendor, or employee. By searching for a location like New York, you can see all related records from each table, choosing which tables to include. Double-clicking a record opens the appropriate form for viewing more details.
Product search forms are covered as well. You can search for products by product code, vendor, ID, cost range, or date range. Searches can be narrowed to certain vendors. The form displays product counts, minimum and maximum values, customer purchase data, and product notes. Double-clicking allows you to view product details or see which customers have purchased a specific product, which is very helpful for marketing or reports.
The seminar also includes a section on natural language searching. This example uses a database with course outlines and enables you to search course descriptions or titles using keywords, exact phrases, or negative keywords. The search results update automatically as you type, and you can use these results to generate a custom report.
If you are interested in mastering search, sort, filter, custom SQL, or VBA-driven searching techniques in Microsoft Access, this seminar offers valuable material for users of all skill levels, from beginners to advanced developers.
For more details about the seminar and to access the free sample database, visit AccessLearningZone.com or go to 599cd.com-xacs. You can find a complete video tutorial with step-by-step instructions on everything mentioned here on my website at the link below.
Live long and prosper, my friends.Topic List Basic find and replace operations in Access
Applying simple filters and sorts
Creating parameter queries for user input
Using form field parameters in queries
Implementing wildcard searches in queries
Displaying blank or null records in search results
Building search combo boxes for record navigation
Creating single field search buttons
Developing multi-parameter search forms
Switching between AND and OR conditions in queries
Dynamically writing custom SQL in forms
Applying multi-field sorts with toggle buttons
Using triple-state checkboxes for null value searches
Designing quick search forms for multiple fields
Adding and sorting recent customers lists
Building advanced search form options
Searching across multiple related tables
Double-click navigation to related forms
Creating product search forms with multiple criteria
Searching products by range of costs or dates
Viewing counts and aggregate values in search results
Linking products to purchasing customers
Implementing natural language search forms
Searching course outlines with keywords and phrases
Live updating of search results as you type
Printing reports from search results |
