Goals
There are four major goals for today's
class.
-
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.

Sample
Database
You can download the database that is
built in this class so you can test it and make sure it's right for your
needs.
Click here to download (Access 2007/2010 format, 32-bit only).

Free
Lessons
The introduction and first lesson of this
seminar are FREE to watch online. This introduction covers most
of what's listed below, including an overview of all of the topics and
concepts covered in this seminar. Lesson 1 goes over basic searching and
sorting.
Click here to play.

Concepts
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.

Access Search Seminar Outline
00. Intro (19:06)
01. Simple Find & Replace (11:32)
Overview of Sample Database
CustomerT
CustomerF
Overlapping Windows
Find & Replace
Access Beginner 4
Find in a Table
Find in a Form
Use Ribbon
Backing Up Tables
Use Bottom Search Box
Find & Replace
Backup Your Data!
Replace All
02. Simple Filter & Sort (11:13)
Filter with column header button
Filtered/Unfiltered button
Apply filter
Blanks
Text Filters
Begins With
Clear Filter
Number and Date Filters
Filter by Selection
Filter Excluding Selection
Contains / Does Not Contain
Filter By Form
AND OR conditions in Filter by Form
Save As Query
Load From Query
Clear Grid
Advanced Filter
Sort Ascending / Descending
Sort by Multiple Fields
03. Parameter Query (13:53)
Simple Query
Benefits of a Query
Find Customers from NY
AND OR conditions
Parameter Query
CustomerListF
Form Design
Form Property Sheet
Record Source Property
Continuous Forms
Form Header/Footer
Change Form Record Source
Use Parameter Query in Form
04. Menus & Buttons (10:45)
Create Main Menu Form
Command Button Wizard
Show All Data
Show Specific Records
Startup Form
Show Customers By State Button
Open Selected Customer
05. Form Fields as Parameters (11:40)
Get Parameter From Form Field
Forms!FormName!FieldName
Search Parameter on Form
Unbound Text Box
Multiple Fields, OR Condition
Multiple Fields, AND Condition
06. Wildcard Searches (9:49)
LIKE Keyword
* and ?
String Concatenation
"*" & Criteria & "*"
Default Values Not Clearing
NULL values missing
Or Is Null
07. Showing Blank Values (19:06)
Null values problematic
Convert Null to Empty String ""
IIF Function
Rename a Query Field, Alias
Show ONLY blank values
Exact or Wildcard Search
Checkboxes
Show Blanks
Search City or State with Blanks
08. Search Combo Box (13:28)
Combo Box Wizard
Find a Record in my Form
Why Find Option Might be Missing
Company Name Search
LastName, FirstName Search
Combo Box Columns
Combo Box Column Widths
Use SQL to manipulate a combo box
Basic SQL Primer
SELECT Statement
ORDER BY for Sorting
09. Embedded Macros (21:03)
Unique list of company names
Group By in a query
Is Not Null
Type Mismatch
What is a Macro
Macro v. VBA Code
Examine Embedded Macros
Open Form Macro
SearchForRecord Macro
Quotes inside of Quotes
Single Quotes
Double Double Quotes
Make a Find Next Button
GoToControl
FindNextRecord
10. Single Field Search Button (11:43)
Wizard Default Search Button
Custom Search Button For 1 Field
Static Search for "XYZ"
User Prompt with InputBox
FindRecord Macro
SearchForRecord Macro
Full WHERE Condition SQL
11. Search Button with VBA (14:25)
Make Backup Copy of CustomerF
Creating VBA Code
Focus
DoCmd.GoToControl
DoCmd.FindRecord
Dim Variable
InputBox
Reset / Stop Code
If Then
12. More Find Buttons (13:05)
Company Name Search
Copy Paste Code
Make Your Own Subroutine
Sub Parameters
Comments
Finding the Next Record
Determine Previous Control
Screen.PreviousControl
Docmd.FindNext
13. Filters with VBA (23:24)
Simple Form Filter
DoCmd.ApplyFilter
If Then Else
DoCmd.ShowAllRecords
Concatenate Many Search Fields
And/Or Combo
Clear Filter Button
14. SQL RecordSource Form (14:40)
Build SQL Statement from Scratch
SELECT FROM
WHERE Clause
Me.RecordSource
Add a Sort Order
ORDER BY Clause
Order By Combo Box
Allow Deletions
Allow Additions
Allow Edits
Allow Filters
15. Dealing with NULL Values (10:52)
Adding IsNull to SQL
Adding NOT IsNull to SQL
Triple State Checkbox
Null Values Multiple Fields
16. Descending Sort Toggles (25:52)
Toggle Button
Button Quick Styles
Background Color
Hover Color
Pressed Color
Toggle.Caption Property
AfterUpdate Event for Toggle Button
RequeryForm Sub
Requery When ANY Value Changed
Sort DESC decending
Run Event when Form Opens
OnOpen Event
Add 2nd Field to Sort By
Definition Jump To
Add a No Sort Option
17. Simple Search Form (22:16)
Easier Form for New Users
Make sure user entered at least 1 item
Message if customer not found
Add new customer with one click
vbYesNoCancel MsgBox
vbYes
With Forms!CustomerF
18. Show Advanced Options (18:07)
Clear button
Tab Order
Determine if an email address
InStr Function
ENTER and ESC buttons
Button Default and Cancel Properties
Get form width property
Resize Form with Button
Me.Width
Twips
DoCmd.MoveSize
19. Recent Customer List 1 (17:43)
Listbox for Recent Customers
CustomerSearchT
On Error Resume Next
Simple Debugging
INSERT INTO SQL statement
Docmd.SetWarnings
20. Recent Customer List 2 (18:03)
Open Selected Customer
Listbox.Column()
OnDblClick Event
On Double Click
See if Customer Exists in Table
DLOOKUP
Look up a value in a table
NZ convert Null to Zero
Delete Query SQL
No Customer Selected on Delete
21. Recent Customer List 3 (17:33)
Change Width of Listbox
No ScrollBars Property for Listboxes
Listbox.ColumnWidths Property
Listbox.ColumnCount Property
Hide the Horizontal Scrollbar
Adjust Col Width in VBA
Format a Date with Format Function
UPDATE SET SQL
Increment In SQL
22. Recent Customer List 4 (25:08)
Sort Labels
Events and Default Values Conflicting
Reload Customer Search List
Order By Labels For Each Column
Default Sort Field
Beware Layout Mode Saving Design
Ascending / Descending Sort
23. Multi Table Search Form 1 (17:30)
Employee and Vendor Tables
Search Results to List Box
Split FirstName LastName to 2 fields
Left, Right, Len Functions
24. Multi Table Search Form 2 (18:41)
Set RowSource of CustomerList on Search
OnDblClick to Open Customer Form
Add EmployeeList, VendorList
Change SQL Statement Based on Which List
25. Multi Table Search Form 3 (24:00)
Create a UNION Query
Merge 3 Tables into 1 Query
Pull All Records from SearchUnionQ
Show Type of Record in Listbox
Select Case Statement
Open Whichever Form is Needed
Select Which Tables to Search In
26. Product Search Form 1 (17:31)
Create Product Table
ProductQ
Outer Join to Show All Products
Vendor Combo Box
Align to Grid
Size to Grid
Empty vs. Null - What to Look For
How to Test Empty or Null
27. Product Search Form 2 (12:02)
SQL for Product Search RowSource
Search Range of Unit Costs
Search Range of Dates Updated
Search Notes Field
Double Click to Open Product
28. Product Search Form 3 (10:50)
Domain Aggregate Functions
DLOOKUP Product Notes
Count of Records Returned
DCOUNT Function
Max and Min Unit Costs
DMAX and DMIN Functions
29. Product Search Form 4 (11:54)
Product purchased by how many customers
Aggregate Query Group By Count
List of customers who purchased product
Docmd.OpenQuery
30. Natural Language Search 1 (14:49)
CourseT Table
CourseF Form
CourseSearchF Form
Search Multiple Fields from One Lookup
31. Natural Language Search 2 (14:46)
Break up string into keywords
OR condition between keywords
Replace Function
32. Natural Language Search 3 (22:41)
Phrase search
Remove quotation marks
Highlight the search phrase in the form
SetFocus
SelStart
SelLength
Negative keywords
Exact match search
33. Search As You Type (18:36)
KeyDown, KeyUp, KeyPress Events
ASCII Character Codes
CHAR, ASC functions
On Change Event
34. Printing Reports (5:08)
DoCmd.OpenReport
Where Condition
Report Events
35. Review (3:21)

|