Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Seminars > Search >
Access Search Seminar

Searching and Sorting in Microsoft Access


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark Buy Now

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

 

Comments for Access Search Seminar
 
Age Subject From
2 yearsShould I take this seminarMichael Duncan
2 yearsSearch SeminarDwight Arroyo

 

Start a NEW Conversation
 
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
 
Subscribe
Subscribe to Access Search Seminar
Get notifications when this page is updated
 
 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 6:15:17 AM. PLT: 0s
Keywords: access seminar microsoft access search, find, replace, filter, sql, vba, parameter, wildcard, natural language, search as you type  Page Tag: whatsnew  PermaLink  Microsoft Access Search Seminar