Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Access Search Seminar

Learn almost everything there is to know about searching and sorting in Access
 
 

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

1. Learn how to perform basic find & replace, filter & sort operations
2. Create parameter queries to prompt users for input
3. Learn how to use form fields as parameters without prompts
4. Perform wildcard searches and learn how to deal with blank or NULL data
5. Search for records using a combo box on a form
6. Understand the difference between embedded macros and VBA code
7. Design a single-field search button to quickly find data in a selected field
8. Learn how to apply filters using VBA commands
9
. Design search forms using custom SQL commands and VBA
10. Manipulate your form search results and modify the sort order of records
11
. Create a simple search form designed for novice users with hidden advanced options
12. Build a recent customer list to quickly open those clients you work with often
13
. Search for data in multiple tables using one quick search form
14. Search for products using ranged criteria (between dates, dollar amounts, etc.)
15
. Quickly view summary information on returned result sets (count, max, min, etc.)
16. Design a natural language search that can decide what type of search you want
17
. Create a search-as-you-type form that updates results with each keystroke

Watch this video to learn more about this seminar:

 

 
Access Search Seminar
Description: Learn just about everything there is to know about searching and sorting in Access.
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: This course stands alone, however you would benefit greatly from having first taken Part One of my Access SQL Seminar. You should have completed at least our Beginner-level Access courses (at least 1-4) before taking this seminar. Our 300-series advanced classes would also be helpful, but not required. I will take the time to explain all of the SQL and VBA commands without assuming you know them already, but it would help to start with those other courses first, if you're planning on learning those topics in depth as well.
Running Time: 9 Hours, 26 minutes
Cost: $199 - Order multiple courses to receive a discount up to 50% off

 

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

 

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.

AccessLearningZone.com

 

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)

 


 

 
 

Student Interaction: Access Search Seminar

Richard on 10/15/2012:  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... 1. Learn how to perform basic find & replace, filter & sort operations 2. Create parameter queries to prompt users for input 3. Learn how to use form fields as parameters without prompts 4. Perform wildcard searches and learn how to deal with blank or NULL data 5. Search for records using a combo box on a form 6. Understand the difference between embedded macros and VBA code 7. Design a single-field search button to quickly find data in a selected field 8. Learn how to apply filters using VBA commands 9. Design search forms using custom SQL commands and VBA 10. Manipulate your form search results and modify the sort order of records 11. Create a simple search form designed for novice users with hidden advanced options 12. Build a recent customer list to quickly open those clients you work with often 13. Search for data in multiple tables using one quick search form 14. Search for products using ranged criteria (between dates, dollar amounts, etc.) 15. Quickly view summary information on returned result sets (count, max, min, etc.) 16. Design a natural language search that can decide what type of search you want 17. Create a search-as-you-type form that updates results with each keystroke You can download a free sample database, watch free lessons, and learn more about this seminar here: Microsoft Access Search Seminar
Jim on 10/16/2012: Richard,

I just looked over the outline of this new seminar and it looks awsome! I was just getting ready to take your SQL seminar but now I think that I will take this one, hoping to kill two birds with one stone. I think that this seminar will teach me some rudimentary SQL while providing me some exciting options for the users of my Ambulance Database. What do you think?

Reply from Richard Rost:

I would recommend SQL part 1 before taking this seminar.

Tiaira W on 10/17/2012: Encrypting the database killed all macros. Not sure if this was intentional...
AlexHedley on 10/31/2012: [2:00-2:20]
The Table changes it's name from OutlineT to CourseT.

Reply from Richard Rost:

Oops.

Jim on 11/2/2012: Richard,

As I contiue to go through this seminar it has occurred to me that your method of teaching has a hidden value, at least to me. You take the time to go over and repeat certain fundamentals of Access assuming that each individual course stands on its own. That allows me to see how these fundamentals can be applied to various conditions and situations. It reenforces that process in my mind. I am sure that others experience the same phenomenon as well. In my case you are the beneficiary of my slow learning process...just kidding. Thanks for all that you do..



Reply from Richard Rost:

Thanks, Jim. I do my best to TRY and make my seminars stand alone, without assuming the student has taken any of my previous courses... but at least has just a basic understanding of tables, forms, etc. So far I haven't gotten any complaints about "repeating" material even though a few of my seminars to duplicate some of the basics. It's impossible to make EVERYONE happy, so I just assume people are OK with a little review. Thanks for the feedback.

Jimmy on 11/7/2012: My question pertains to Access Search Seminar Lesson 24 time index 2:37, when you did your search for "rick" and there was no record. Is there a way to have the code fire a MsgBox "Record Not Found." as opposed to just leaving the list box blank?

Very Respectfully,
Jimmy Mitchell

Reply from Richard Rost:

Sure. Keep watching. In one of the next couple of lessons I show you how to put counts of the number of records found in a box below the list. You could just use an IF statement to pop up a message if the count is zero.

Scott Adkins on 11/8/2012: Richard, I dont know how I keep getting all these screwy problems. At 2023 in lesson 9.
When I use the find next for the command button, I get a different error than you.
It says "You didnt specify search criteria with a find record action. In the macro windo insert a Find Record Action before the Find Next action.
So i tried the simple solution and put the goto control (as in the video) and get the same error.

I then put in the find record, and it will only find the first record that matches, customerid 2

Reply from Richard Rost:

I can't reproduce this. Is anyone else having these problems?

Scott Adkins on 11/9/2012: I just downloaded the sample file and I get the following error:
Run-Time Error 2143:
you didnt specify search criteria with a find record action.

Reply from Richard Rost:

What versions of Windows and Access are you working with?

Scott on 11/9/2012: 2010

Reply from Richard Rost:

OK, I figured it out. This form control was effectively broken by stuff we did in a subsequent lesson. All you have to do is add one command to the embedded macro for the CompanyNameSearch control. After the SearchForRecord command add:

GoToControl, CompanyName

That should fix it.

Richard on 11/9/2012: IMPORTANT NOTE: If you've downloaded the sample database from my web site, the search combo box control created in this lesson was effectively broken by stuff we did in a subsequent lesson. All you have to do is add one command to the embedded macro for the CompanyNameSearch control. You'll understand all of this better after finishing the lesson - and even moreso after finishing the whole course. Sorry.

After the SearchForRecord command add:

GoToControl, CompanyName

That should fix it.

Scott Adkins on 11/9/2012: Richard, I tried putting the GotoControl in and it is still doing the same thing! I am going to try to re-do the lesson, but do you have time to explain exactly what causes the problem? In the meantime, i'll redo the lesson and go from there.
Sam Finlay on 11/13/2012: I'm having the exact same problem as Scott, including the original "different error message".
Tried your suggestion Richard but problem still persists. Any ideas ?

Reply from Richard Rost:

Did you add the "GoToControl, CompanyName" command as I indicated in my previous post?

If you build the form yourself, following along with the video, you shouldn't have this problem. It works AT THE TIME in the video. However, some of the things we do LATER in the seminar effectively "break" this form... so if you download the sample database and try to use it AS IS, then yes, you'll get this error message.

BUT... I strongly recommend that you BUILD THE DATABASE YOURSELF. You'll get a much better understanding of what's going on.

Sam Finlay on 11/13/2012: Hi Richard
The thing is I HAVE been building the database as I go along ??
Tried your suggestion but no joy. In fact, I tried the same thing on another laptop of mine with Access 2010 installed and still got the same thing.(although, admittadly using a copy of the database I mAde on the first laptop)
I've been over and over your video a good few times and tried to follow it as perfectly as I can.
Not sure if this matters but I need to use "Double- double " quotes in the CompanyNameSearch combo box , to get it to work !! Also, while the embedded macro for thIS combO box seems to work on the customerF form (ie it will change the record based on company name), if I run the macro on its own it still says "Type Mismatch"
It's driving me crazy so any other help very much appreciated.

Sam F on 11/14/2012: I think I may have a possible solution.
First add the command to the embedded macro for the CompanyNameSearch control. After the SearchForRecord command add: GoToControl, CompanyName as suggested by Rick.
Then, instead of using FindNext in the "Next" command button, use :

SearchForRecord - Object type and Object name leave blank
Record - next
Where condition - ="[CompanyName] = '" & [Screen].[ActiveControl] & "'"

It works for me ! Maybe Scott could let me know if this works for him as well ?
Course, the question still remains WHY it should be different in the first place ?

Also Rick : In the downloaded DB, I couldn't get the VBA to work on clicking the "Next" button. What I did was, write the above macro, convert it into VBA and replace it with what was on the buttons onclick event. Hey presto it worked !!


Reply from Richard Rost:

Interesting. I'll play with it when I get a few minutes.

Jim on 11/23/2012: Richard,

I have created a search form that searches my ambulance run table for a variety of data. One of the pieces of data in the table is the time of day the call was dispatched. I would like the ability to check which runs were dispatched let's say after 10pm or between two times, something like that. I am have defined two variables in the form, "PageOutMin" and "PageOutMax" and check them against the field "PageOutTime" in the RunT table. I can't seem to avoid a syntax 3075 error when I enter 10PM or 22:00 into those fields on the search form. Could you please shed some light on this? I know it's a simple thing but I just can't seem to figure it out...

Thanks very much.

Reply from Richard Rost:

That syntax error usually means that your criteria is invalid. It usually comes up when you forget to put a date field inside of # symbols in SQL queries (or quotes for strings) like this:

MySQL = "SELECT * FROM OrderT WHERE StartDate>1/1/2001"

When it should be:

MySQL = "SELECT * FROM OrderT WHERE StartDate>#1/1/2001#"

Now if you just want to check the TIME and you don't care about the date portion, you can separate out the HOUR of the date field using the HOUR function:

MyHOUR = HOUR(SomeDate)

Now you have a value from 0 to 23 that you can use for your reporting.

Jim on 11/25/2012: Richard,
Thank you as always for your help. I seemed to have resolved the issue by creating an alias in the supporting query for the search form using the timevalue function. I then inserted this alias into the wh string for pageouttime and it seemed to work without issue. I really wanted to check times beyond just the hour portion and this method seems to work for that. I hope to learn enough some day to be exposed to all the funtions that are available. It will just take time and perserverance...Thanks again for taking time to respond to my questions.

Jim

Reply from Richard Rost:

You're very welcome. Yes, I'll be spending a LOT more time on functions in Access in the Expert series... coming soon.

Dana Michaels on 12/2/2012: I would like to be able to clear the Customer Name combo when I chose a CompanyName and vice versa

Reply from Richard Rost:

You could do that with a little AfterUpdate event in each combo box. For example, in the Customer Name combo box, just say:

CompanyNameCombo = ""

(or you might have to say CompanyNameCombo = NULL depending on your data).



Sam Finlay on 12/3/2012: Hi Richard. In Lesson 13 when the filter button has the code : DoCmd.ApplyFilter , "FirstName = "" Rost """

There are no concantenation symbols (ie & ) and the code runs.

Yet, when using the code: DoCmd.ApplyFilter , "FirstName = """ & FirstNameFilter & """"

The code won't run without the two ampersands ? Why are they needed in the first place ? It's not as if you are actually concatenating (joining) any strings of text.
Just curious.

Reply from Richard Rost:

So I don't have to watch the whole lesson again, what TIME INDEX are you looking at? (mm:ss)

Sam Finlay on 12/3/2012: Apologies, It's from around 6 min 30s to 8min 13s
Alex Hedley on 12/4/2012: The first option is a hardcoded value. It was used as an example to show you how the Filter works.

The whole thing is wrapped in quotes " ".

DoCmd.ApplyFilter , " FirstName = "" Rost "" "

That means all of it is a one string.

The second option which uses &s has these because you are using the value in the control so it substitutes [FirstNameFilter] with for example "Alex" that you have written in the form.

Private Sub Command8_Click()
DoCmd.ApplyFilter , �FirstName=��� & FirstNameFilter & ����
End Sub

Al

Reply from Richard Rost:

Whoa... somehow your post got garbled. No idea why. Can you repost the last bit of code?

Alex Hedley on 12/4/2012: It was just a copy of what Sam had written so everything wrapped in quotes and the double quotes for a single quote to show in the overall string.

DoCmd.ApplyFilter , "FirstName = """ & FirstNameFilter & """"

[I copied that from the handbook, on my phone granted but �ber odd]

Reply from Richard Rost:

Not sure why the new web site is doing this to some characters, but it looks like it happened again in your comments. Odd.

Robert Miller on 12/7/2012: Video plays to a little over 10 minutes in and then fast forwards to the end of the video. Anyone else having issues? I also could not watch the last 4 minutes or so of lesson 32.

Reply from Richard Rost:

Robert, I just checked and it's working fine for me. It could be a problem with your browser cache (assuming you're watching online). Close your browser completely and then reload the page. Let the video re-download. Nobody else has complained about this issue so it's probably a download problem.

Robert M on 12/7/2012: I tried using a different browser, Firefox, and had same issue. I even downloaded the wmv file and it wouldn't play either. I'm trying to access from a rather old computer at work so I will try again this evening from my pc at home and let you know if still having issues.

Reply from Richard Rost:

Yes, please do. If the file isn't downloading correctly then your browser wouldn't matter. I tested it from here, but I didn't see the problem. Usually if there is a problem with a video online a BUNCH of people yell at me. If it's only one person yelling at me, I suspect it's their computer. :)

Francois M on 12/8/2012: Richard I had the same problem but I installed a new codec pack K-Lite Mega pack that sort out the problem.

Reply from Richard Rost:

Yep. Updated CODECs will usually fix the problem. Thanks.

Jacob Snyder on 12/14/2012: I have created a database that contains a lot of Yes/No Check boxes and I am struggeling to delvelop a method for finding all of the yes options from a Search From. Do you have any tips or training sessions that are associated with this?

Reply from Richard Rost:

How do you want to go about finding the yes/no options? Do you want to search for all of the records that have ANY yes options? Tell me more about your search needs.

Robert M on 12/14/2012: Rick, forgot to follow up with you on this. I got a new computer at work and everything is working great now. I thinkg it was just due to the age of the computer I was using. GREAT seminar!! Thanks for all you do!

Reply from Richard Rost:

Awesome. Glad you're enjoying it.

Bea Anderson on 12/16/2012: I upgraded from Access 2007 to 2010. I wasn't using a date picker in 2007. I can't get the calendar to come up in 2010. I have set up my date text box as short date and date picker, but it doesn't work for me. Video 27, 06:50

Reply from Richard Rost:

Did you see the ADDENDUMS for this seminar?

I believe one of them addresses this issue.

Bea Anderson on 12/16/2012: I went to Create, VBA, Tools, Reference and there was no Missing Calendar Control 2007. I set up a new date and format short date and datepicker for dates. I still don't get a calendar.

Reply from Richard Rost:

Can you get it in a NEW, BLANK database? It could be a problem with your upgraded db file.

Bea Anderson on 12/17/2012: It works on a new data base. How do I fix the old data base?

Reply from Richard Rost:

That can sometimes take a lot of troubleshooting. The EASIEST way to move forward is to just create a blank new database and IMPORT all of the objects into it from your old database.

Bea Anderson on 12/17/2012: Thanks, that is what I decided I would have to do, since there may be more problems. Thanks for all your help. I have learned so much from your videos. bea
Bea Anderson on 12/17/2012: FYI: I moved all the objects to the new blank data base. I had to delete the date fields on the form and add them again before the date picker worked. Thanks again for all you do.

Reply from Richard Rost:

Glad you got it working.

Becca Zebovitz on 12/31/2012: I am having a similar issue. I have a database for organizations my organization interacts with. Part of the information we collect is the services offered by each organization. The idea is that if someone calls the organization for services we do not offer, we can look up who in our orbit we can refer them to. This information is stored in Check boxes. I am trying to create is a form where the user can check off the services they are looking for and have a query bring up all the organizations that offer each service. Basically what you describe in your Multi-feild search form tutorial, but using check boxes rather than text boxes. Do you have anything that covers this? Thank you!

Reply from Richard Rost:

The techniques covered in the Search Seminar will show you how to do this. We use several different methods, and the "build an SQL recordsource" will allow you to EASILY accomplish this goal.

Norberto Arana-Soto on 1/17/2013: Hi Rick,
In lesson 9, index 17:32 of the Search Seminar you show us how to create a NEXT button to navigate to subsequence records of the same company. How could I add a field in the form to show me how many records of that company are in the database?

Reply from Richard Rost:

You could use a DCOUNT command in a text box to figure this out for you:

=DCOUNT("*","MyTableT","CompanyID=" & CompanyID)

Just put that command (change the names accordingly) on your form in a text box (as the ControlSource) and it should refresh with each record.

Emmett on 2/5/2013: In the lesson on Form Fields as Parameters, you show how to build a field in which you type a parameter for the search button to act on. Is it possible to use a Combo box from which the user can select a value? i have a small group of districts that I would like to display for the user so that he doesn't have to type anything. Thanks
Bruce Reynolds on 2/15/2013: For the line:
if X = "" Then Exit Sub

Would:
On Error Resume Next

do the same thing?

Bruce Reynolds on 2/15/2013: For:
Private sub MySearch(ControlName As String, Prompt As String)

is ControlName and Prompt reserved names within this private subroutine? In other words, could you restate the subroutine as:
Private Sub MySearch(ControlNameSearch As String, PromptSearch As String)?

Reply from Richard Rost:

They're not reserved words. You can rename them as you like.

Alex Hedley on 2/16/2013: Bruce, they aren't the same,

"On Error Resume Next"
is a way to make sure your code continues even if there is an error, it just skips over it and goes onto the next line of code.
The whole routine would complete.

if X = "" Then Exit Sub
is actually checking the value of X and if it's blank then STOP and don't do anything further.

Bruce Reynolds on 3/2/2013: Is there a metric equivalent to the TWIP?

Reply from Richard Rost:

Good question. A twip is 1/1440th of an inch, so doing the math it comes out to 1/567th of a centimeter.

Bruce R on 3/2/2013: Thank you, but to take this further, is there a metric equivalent function that is similar in nature to the TWIP function that does something equivalent to an Access developer in say, France?

Reply from Richard Rost:

I don't think so... but to be honest, I do very little work with metric units. Here in the States we're backwards and haven't joined most of the rest of the planet with the metric system yet. I would refer you to Wikipedia or a Google search for your answer. In the mean time, I'll be stuck using feet, miles, pounds, and twips for the near future. :(

Bruce R on 3/2/2013: An interesting article is available on:
http://social.msdn.microsoft.com/Forums/ta/accessdev/thread/f64af695-21f7-421f-9c7c-b398e15ab085

Bruce Reynolds on 3/11/2013: Good coverage on UNION queries! Much appreciated!

Reply from Richard Rost:

Great. Glad you enjoyed them. I've got a few more tricks with them coming up when I revisit them for the 2010/2013 series.

Bruce Reynolds on 3/11/2013: What's the Gigabyte database file limit of Microsoft SQL server?

Reply from Richard Rost:

You'll never hit it. It's 524,272 terabytes per database. :)

Bruce Reynolds on 3/11/2013: The site on Microsoft says that the size limit for Microsoft SQL server is 2 Terrabytes for the File Size (log), and 16 Terrabytes for the File Size (data). The question now becomes, what is the fundamental difference between File Size (log), and File Size (data)?

Reply from Richard Rost:

I'll be completely honest... I'm not entirely sure. I'd have to read up on it some more. I'm a little rusty on my SQL Server since I haven't worked extensively with it in the past 2 or 3 years. I focus more on PRACTICAL applications rather than the trivial things like this... although it is good to know. I just know that for 99.9% of the databases I'm ever going to build or teach people to build, I'm never going to hit that limit. The maximum DATABASE size is 524k terabytes. Since a database can consist of multiple files, there's a 16 TB limitation per FILE. Since there's a 16 TB file size limit under WINDOWS, that makes sense. But just like you can link multiple Access databases together, you can chain multiple SQL server "files" together to form a master database. The 2 TB limit is probably the LOG files (for server maintenance) but again, I'm not entirely sure. If you've got 2 TB of log files sitting around, it's time to do some analysis and deleting. :)

Bruce Reynolds on 3/11/2013: Ironically, Google does not make use of a relational database, but uses a multidimensional database called BigTable, which is built on the Google File System (GFS). Google's search algorithm is so complicated (or advanced), that it needs a specialized type of multidimensional database to support its specific needs.

Reply from Richard Rost:

I did not know that. Thanks for the tip! I'll have to read up on it some more. Web search has always fascinated me.

Bruce Reynolds on 3/13/2013: In this lesson, the code works, but the Wh string which is combined in the SQL statements and the dizzying array of double double double double double quotes makes my head spin!

Reply from Richard Rost:

Yeah, it can be confusing at times. Just remember in order to put a DOUBLE-QUOTE inside of a string, you need DOUBLE-DOUBLE-QUOTES. Sometimes even I write it out in Notepad first without them, and then just substitute.

Michelle C on 8/21/2013: Richard, for myself, you would be correct in this assumption. Thanks for repeating yourself. ;-D
James Gray on 4/7/2014: Rick,
My wife and I have run into a stuation when we both want to have unique identities on certain websites (e.g. the national emt registry) They use email as a unique identifier like you do. How do you accommodate those folks that may share an email? In our case we were forced to set up a second email account for my wife in order to register ourselves.
Thanks as always Rick for your time.

Reply from Richard Rost:

Well, setting up a 2nd email account is no big deal. I personally use Gmail for most of my email. One nice trick with Gmail is that you can set up aliases like this:

personal+amicron@gmail.com
business+amicron@gmail.com

See what I did there? Everything before the + is an alias. You can use it to direct mail to different folders with filters. You can use it to track and see if a particular site is giving away your email address. Lots is possible.


Jim on 4/8/2014: Rick,
That really sounds too easy, I guess I never really thought it through. I have always been of the mindset that email is a way into my computer and have tried very hard to limit my exposure. Thanks for taking the time to answer my question. Once my budget gets into a little better shape I will be ordering the email seminar too...

simon b on 7/8/2014: Richard

I am having the same problem i am using 2013, macro will only find the first record and thats it. I had to delete the button 3 times and redo it before i got the same error message as you.
Any ideas?

Debbie Fortin on 9/8/2014: I have completed the search function successfully but am wondering if there is a way to add column names to the results field.

Reply from Richard Rost:

You can add whatever additional fields you want.

Tom Dlugosh on 11/28/2014: It seams that if a field in a table contains a # sign the search code doesn't function properly. Is there a way around this other than not to use this key in a data field?

Reply from Alex Hedley:

I wouldn't put them in Field names, can you remove it?
Try putting [] around the field

Tom Dlugosh on 12/1/2014: Can this procedure be used with a combo box?

Reply from Alex Hedley:

Hi Tom,
Which procedure?

Tom Dlugosh on 12/2/2014: I'm trying to use combo boxes for searching, but I get an error msg that the combo box doesn't have focus. I can't seem to force the focus with SetFocus or DoCmd.GoToControl methods. Any suggestions?

Reply from Alex Hedley:

Is the combobox in your Form Header?

Can you try this tip and see if that works

Tom Dlugosh on 12/3/2014: It is in my header.

Reply from Alex Hedley:

Is the combo BOUND to a Field in the Table?
I've copied one on a Form before and forgotten to unbind it to a Field.

Tom D on 12/3/2014: The Simple Search in lesson 17.

Reply from Alex Hedley:

Yes, just have the combo value be the search criteria instead of the Textbox.
If you name them the same as the Textbox it'll work just like the video.

Tom Dlugosh on 12/5/2014: It was bound, but removing the binding doesn't change the operation.
Tom Dlugosh on 12/5/2014: Okay, I figured out that the combo box was bound to a field, but when I unbound it it stopped operating as a drop down box (of course). I was trying to get the combo box to act both ways, but it doesn't appear that's possible. I guess I'll have to decide on which way I want to search a field.

Reply from Alex Hedley:

Tom, up binding it doesn't remove the data source.
You can set the combo to have a Row Source of a Table/Query or a List.this will usually be a Key Column and a Text to display.
You then usually bind it to a Field that the Form has set. i.e. A number field so you can create your relationship.

When using a combo as a search tool you keep the record source the same but isn't of binding it to a Field on your Form you leave it unbound and use the value of that In your search criteria.


Tom Dlugosh on 2/2/2015: Richard,
I've been fighting this for a couple of months and I can't find a solution.

I have a search procedure that is designed like yours and works great, but I'm trying to do more (of course). I have several combo boxes to progressively search a materials table. What I'm trying to do is use these combo boxes as input boxes when an item is not in the table. As soon as I type something that isn't a part of the table the combo box loses focus (even though the statement directly above the .SelStart statement is a .SetFocus statement) when I get to the end of the table. I can use a SendKeys("{F2}") statement to replace the .SelStart statement to essentially accomplish the same result, but that creates all sorts of other problems. Any suggestions or am I trying to do too much with this procedure?

Tom

Reply from Alex Hedley:

Are you wanting the item to be added to the Table
Like a not in list event? (Access 324)

Matthew Corlies on 3/22/2015: I track lease return copier. I would like to have a query that would provide the copiers that are due to return in 30 day or less. I have a field in the check-in form that provides the end of lease date.

Reply from Alex Hedley:

You could add a criteria of < LeaseDate + 30

Robert Baxter on 5/4/2015: Hello Richard. One thing you do which I really appreciate is the inclusion of "mistakes". Unfortunately, I've watched many YouTube Access tutorials where the instructor makes a mistake, or gets and unanticipated result, BUT viewers can tell from the instructors tone, or dead silence, that it was a true mistake. Sometimes they troubleshoot it on screen, but often they will pause the recorder and fix the problem. By contrast, your "mistakes" are clearly intentional as a teaching tool. Personally I think it enhances the tutorial because it gives me some thing to check when I make an actual mistake - which is often where VBA is concerned. Thanks again.
Deon R on 5/6/2015: Hi Robert, and astute observation. I always enjoy the 'mistakes' Rich throws in from time to time. It definitely has made me a better programmer. Keep them coming Richard. We can't wait for the next Access lesson or Seminar. Cheers for now, Deon
Richard on 5/6/2015: Thanks guys. I've always found that I learn best from seeing other people make mistakes, so I try to include them in my videos whenever possible. Most of the time, these mistakes were ACTUAL mistakes I made at one point (usually years ago) when I was first learning how to do this stuff myself. If I stumbled on it, I figured you guys probably would too... so it would be nice to show you what to avoid, and how I got around it. Glad you appreciate it.
Robert Baxter on 5/6/2015: As I was watching how to use the "binoculars(find)" I thought, where else do you get this stuff but here at 599CD? Thanks.

I know you are going to take us far beyond this one tool in the seminar, but in the interum I have an idea of how to put the binocular Find tool to use tomorrow. Thanks.

Richard Lanoue on 5/7/2015: I have a problem, in my database that deals with film and Tv shows... I have titles for example:

"Star Trek episode # 12"

the "#" really throws a kink in the search giving me NO RESULTS in my list box. How do I get the search to recognize the "#" or any other non alpha/numeric character for that matter?

Also on some of my forms if certain fields are left blank because of no data available, I notice they don't show up in the search as well. why is that?

Thank you
Rick

Reply from Alex Hedley:

Since # is used for dates this will not work.
You'll need to surround the # with square brackets ( [ ] )

This goes for

  • [ => [[]

  • ] => []]

  • * => [*]

  • ? => [?]

  • # => [#]



You can use the REPLACE function to change the string you enter before you pass it into your query.


Do you have an OR or an AND in your Query Criteria.
Are you checking for IS NULL?
OR - Down
AND - Across

Richard L on 5/8/2015: That's pretty disappointing news.. I want the "#" in the title. Is it possible, based on the search seminar SQL VBA setup" to take a "SearchTerm" and say

if a "#" exists then S= SearchTerm &"["&"]" something like that else S=SearchTerm.

something like that because I don't want to upadate querry and remove it from the title?

PS I based if my search form on Product search lesson in the search seminar

Reply from Alex Hedley:

You can keep the # in your Field in the Table, I meant you would need to wrap the # in [] when passing it to the search function.

Just need to sanitise the search term before using it.
SearchTerm =REPLACE(SearchTerm, "#", "[#]")

Robert Baxter on 5/13/2015: Hello Richard. This lesson is a good first stab at how to make practical use of VBA. But I seem to have a small issue with the NEXT button after changing from a macro to VBA.

When the CustomerF is first opened, the Company Name combobox at the top is blank. No problem, so I selected XYZ Corp from the dropdown to check workings of the NEXT button. No go. Even with "XYZ Corp" in the Company Name Field, the NEXT button doesn't show me the next XYZ Corp name (Like Pete Smith). So I clicked on the binocular FIND button next to Company Name that we just converted to VBA, then the FIND button cycled to the next record from XZY Corp.

It seems like the NEXT button does not set the focus to Company Name like the binocular Find button does. Is there a way to correct this?

Thanks!

Reply from Alex Hedley:

You could add a
CompanyName.SetFocus
To your converted code.
Is the code that was converted referencing the correctly named contols? None have changed name since. It sometimes hardcodes them

Robert Baxter on 5/26/2015: Good Day Richard. As per your suggestion to students back in Access Beginner, 1. watch the lesson all the way through first. 2. Then go back to perform the tasks in the lesson.

Because of that, I was able to sidestep the "Layout mode" issues you ran into between 11:25 and 21:38 of the lesson. So I skipped the code manipulations you did to correct that problem.

Here is my question: Does the work you did to correct the Layout mode problem (11:25 to 19:25) affect setting the listbox labels to sort by their various columns?

I ask because I created the sort buttons for First, Last & email fields before the Layout mode issues at 11:25.

Then I copied the same 2 lines of code as used for "First","Last" & "email" buttons, when creating the "Time" button sort(at 21:38). I changed the code to read:

OrderBy = "LastAccessed DESC"
ReloadCustomerSearchList

When I saved, closed, and reopened the QuickSearchF I got a Parameter Query window asking for input for "LastAccessed". I assume that means Access does not recognize "LastAccessed DESC". Also I get the Debug Window and yellow hi-lite at the line:

OrderBy = "LastAccessed DESC"

It is a simple line of code, so I am confused.

I have done this lesson from start to this point (21:45) twice, with the same code error message/hi-lites coming at the same place in the process.

Prior to the sort button code at 21:38, things were going quite smoothly with the work we are performing on QuickSearchF.

Hopefully the above makes sense. Any comments are welcome.

Regards,
Bob


Reply from Alex Hedley:

Does the Field LastAccessed exist in the RecordSource of the Form?

Richard Lanoue on 5/28/2015: I have a search need to get the EXACT Name...No LIKE function

It's currently this:

Wh = ""
If IsNull(SearchTerm) Then SearchTerm = ""
S = Trim(SearchTerm)
If Left(S, 1) = """" Then ' whole phrase search
S = Replace(S, """", "")
Wh = "FilmName LIKE ""*" & S & "*"""
ElseIf InStr(S, " ") <> 0 Then ' have spaces
Wh = "FilmName LIKE ""*"
Wh = Wh & Replace(S, " ", "*"" OR FilmName LIKE ""*")
Wh = Wh & "*"" "
Else ' no space
Wh = "FilmName LIKE ""*" & S & "*"""
End If
If Wh <> "" Then Wh = "WHERE " & Wh



I want to remove the likes but it doesn't seem to work after...How do I make it so that it is the exact Film Name?

Reply from Alex Hedley:

Change
"FilmName LIKE ""*" & S & "*"""

to
"FilmName='" & S & "'"

Richard Lanoue on 5/30/2015: I created a search where certain criteria are looked for...First Name, LastName, Race, Gender, address etc...
some of these forms do not come up in the search.. For Example,
I search for Rost and I know your record is there but if some fields are left blank (an unknown) fore some reason you won't come up in the list box results list.... why.... what causes this search blindness... how do I get around this?

Reply from Alex Hedley:

Did you do AND across, OR down?
Are you adding IS NULL in the OR?

Richard L on 5/31/2015: I am using in the SQL multiple tables and some have null values...I created a list in the combo box "Unknown" and did an update query to make null values "Unknown"... still no luck.

Reply from Alex Hedley:

What is the SQL you are using?

Robert Baxter on 6/1/2015: Hello Alex. Thanks for your time. To answer your question directly: No, the RecordSource of the FORM (QuickSearchF) is the Customer table (CustomerT). At least it is now - because in order to try to keep going in the seminar, I went back 2-3 lessons and rebuilt the QuickSearchF from scratch.

The second time I arrived at 21:38 in this lesson without that problem. QuickSearchF seems to work fine now. So I must have done something the second time that I didn't do the first time.

Interesting note: I encountered a similar "parameter request" after creating ProductSearchF in lesson 26. The form we built in lesson 26 was built on top of a previous form we created. So I saved the entire code, then rebuilt ProductSearchF from scratch as well - then pasted the code into it. The "parameter request" went away. It would seem there is a step that I am continually missing. But it works out the second time. Thanks again for your time.

Reply from Alex Hedley:

That Field needs to exist in the RecordSource for you to be able to sort by it.

Richard L on 6/3/2015: See if this sheds some light on the problem... I did a search subroutine in SQL. It uses two or more tables, A primary data will call CustomerT and a secondary Table for additional info. If data is in the primary and secondary Tables.. all is fine. But if ONLY THE PRIMARY CustomerT has data but the secondary IS NULL then the search results shows nothing. What can I do to still force it to show up in the listbox results? In a previous lesson I think I took do you know where you can point me?
Thanx

Reply from Alex Hedley:

This will be to do with your JOIN.
When you have joined the two tables, double click the line, this brings up a box.
You can show records where they match or show records in one table, or the other.

Wayne on 6/14/2015: Lesson 20 seems to truncate the last few seconds of video. No biggie.
Robert Baxter on 6/14/2015: I hope this is not too vague a question: at 9:16 you talk about adding the capability to update the entire dataset automatically when ANY change is made by Filter/Sort/OrderByCombo "if you are dealing with a small sets of data".

Does that imply that capability is NOT a good thing when working with large datasets? And can "large" dataset be defined?

Thanks, Bob

Reply from Alex Hedley:

I'd say large would be in the 100s of thousands of records.
Obviously there is a time factor given the size of the dataset

joe schwa on 11/30/2015: when I try to apply a filter (I follow all steps the same the class)
I get an error '2491'
Saying the action invalid because the form isn't bound to a table or query


Reply from Alex Hedley:

What is the Record Source of the Form?

You could use Me.Recordsource = with a WHERE Clause instead

Richard Lanoue on 12/24/2015: I like this setup using vb code and sql in the results list. Such as ResultsList.RowSource = SQL. BUT I want to also generate reports with the record source from SQL where the main search form input is from a Main Form. I want to get away from using ReportQ and get my Where Conditions from Vb Code.

Reply from Alex Hedley:

The SQL courses would be handy here.

Richard L on 12/24/2015: Yes, but I can't recall where in the lesson. I can make it appear in listboxes for users to view, BUT how do I get it to a printed hard copy? I could use a Querry using Forms!InputF!FieldName. But I really like the vb code to use the where conditions.

Id Dim SQL as String
and Dim Wh as String

do something with the input

SQL = ....

List.rowsource = SQL

Now I want that data to print a report on ReportR with these SQL and Where Conditions


Reply from Alex Hedley:

So you want a Report showing the SQL used to get the records or a Report showing the records that the SQL retrieves?

Richard L on 12/30/2015: Yes, I would like to avoid using a Query for a record source, if possible


Reply from Alex Hedley:

Which one was it?
You want to set the RecordSource of a Report in code
You can have a button that opens the Report then set the Reports RecordSource.

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = ...
End Sub

Richard Lanoue on 1/14/2016: What I want to do is put a StateCombo Box. On the Querry, Forms!InputF!StateCombo criteria under StateID...Works great until I leave it null. In that case I want to show all the states... In VB Code I can do this but I can't in SQL... Seeing how I want to create a querry to export into Excell, I need to know how to make it so I can select all the records or some by state. Any tips advice? The State: StateID & "" I don't seem to have any success with. In short if the Combo is null than pretend there is no code under the criteria and show the records of all the states as if it weren't a factor.


Reply from Alex Hedley:

Can you not use VBA?
If Me.YourCombobox = "" Then
'Place code here to address selction of
End If

You could have a * as the all and pass that to your query with a LIKE

Rick L on 1/18/2016: The reason why I can't use vb code is because I need to build a query so I can export it to an Excel Speadsheet. The Forms!InputF!StateCombo works great if you select something but if I leave it null, I get none of the records when I want records from all the states
Richard L on 1/22/2016: I can do it with VB code no problem... but maybe this might be easier, How do I transfer the result not only into a listbox rowsource BUT to an excel spreadsheet... TransferSpreadsheet acExport function Ultimately this is my problem


Reply from Alex Hedley:

You can use QueryDefs to create a Query on the fly.
Did you try the * trick for ALL?


Create a String of where you want to save it to and the name:
strExcelFile
Choose the correct 'SpreadsheetType'

expression .TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QUERYNAME", strExcelFile, True
https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

AcSpreadSheetType Enumeration (Access)
https://msdn.microsoft.com/en-us/library/office/ff196017.aspx

Richard L on 1/22/2016: The problem is the query doesn't exist... its a string generated on a form and the SQL is in the listbox (Search Seminar- lesson 23)

Reply from Alex Hedley:

I've just taken a look at the Search Seminar sample db, it's been a while since I've watched it so had to fresh my memory.

If you look at the CustomersFromStateQ you could match that, so have a checkbox on your Form that says Blanks then add this criteria to your Query:

Like IIf([Forms]![MainMenuF]![ExactState],[Forms]![MainMenuF]![State],"*" & [Forms]![MainMenuF]![State] & "*") Or IIf([Forms]![MainMenuF]![BlankState],"")
Replacing names with your Form and Field Names,

If you have a SQL String instead of a Query use the principles from CustomerListSQLF
Again with a checkbox for show Blanks


If BlankFirstName = True Then
If Wh <> "" Then Wh = Wh & " " & AndOrCombo & " "
Wh = Wh & "IsNull(FirstName)"


Richard L on 1/22/2016: The Like * didn't work,,, unless I did something wrong. Do I have to make it a Query?... Can you export SQL Variable as opposed to a Query to generate an Excel Spreadsheet
Richard L on 1/23/2016: Thank you very much

Reply from Alex Hedley:

Happy to help :)

Richard Lanoue on 7/1/2016: I would like to have a class on error handling topics...

Reply from Alex Hedley:

There are some mini lessons on error handling, but a mini seminar or class on this specifically would be a good idea.
I made a tip on Custom Errors

Ezell Martin on 8/2/2016: Sir,

I have a question on the part of using combo boxes to filter a list and display a report to print. I have a query which works great to filter out the data I need from a specific company I choose after selecting that company from a list in a combo box. The combo box gets it's values from a table inside the database. I also have a second combo box which also gets it's values from the same table inside the database the first combo box uses in order to get more specific data from out of that particular company and would like to display that data on the report. For example I have company "A", "B" and "C". Each company has a platoon "A", "B" and "C" inside it. I want to see a roster from company "B" from platoon "C" and display this data in a report from a single query, but I can't get the query to work right when I include the second combo box. When I have the query designed with just the first combo box filter for the companies inside the query does exactly what I want it to do, but when I add the second combo box filter for the platoons then the query doesn't work. I want the filter to give me data of the company and platoon I select in the combo box and if I leave the combo box blank for example I want it to give me all the companies (or platoons) in the appropriate combo box. So is I select company "B" in the first combo box and leave the second combo box blank I want to see all data from all platoons (meaning "A", "B" and "C") whom are under company "B" in a single report. If I select company "B" and platoon "C" then I only want to see the data from platoon "C" under company "B" in a single report. If I select platoon "C" in the second combo box and leave the first combo box blank I want to see data from platoon "C" from all platoons (meaning "A", "B" and "C"). If I leave both combo boxes blank I want to see all data from all companies and platoons.

The "LIKE" statement I used in the criteria of the query for the company field is:

Like [forms]![Administrative Reports]![Company]

I used the following expression in a field with the criteria stating --> Is Null <-- in the same query for the company:

Expr2: [forms]![Administrative Reports]![Company]

The "LIKE" statement I used in the criteria of the query for the company field is:

Like [forms]![Administrative Reports]![Platoon]

I used the following expression in a field with the criteria stating --> Is Null <-- in the same query for the company:

Expr2: [forms]![Administrative Reports]![Platoon]

If I could get the query to work right then everything else would fall into place and I could form the other reports I have generating other data on these companies and platoons to work right. I could use your expertise on this one. I figure it's something simple and I just am thinking on it too hard. When I have one search/filter combo box the quest works great. But when I added in the second search/filter combo box to get more exact data down to the platoon level I continually have issues and can't figure out how to get it to work. I reviewed your search and MS Access Expert level classes I purchased form you and still can't figure it out. Please get back with me on this ASAP. Any assistance you can provide me will be most appreciated. Thanks for your continued support and all that you do!

very respectfully,

EZELL MARTIN

Reply from Alex Hedley:

If you're using a LIKE you usually have a Wildcard (*) before and after the search term.

"SELECT * FROM x WHERE y LIKE *" & Term & "*"

Ezell Martin on 8/6/2016: Hi,

I am using the following expression in the criteria portion of the query in the query design mode:

Like [forms]![Administrative Reports]![Company]

[Company] is the name of the first combo box and [Platoon] is the name of the second combo box on the administrative reports form footer. When I have this LIKE expression in the criteria portion of the query in query design mode without adding the [Platoon] LIKE expression the query works fine. When I add the [Platoon] LIKE expression in the criteria portion of the query in the query design mode it doesn't pull the data I ask in the example I gave in my initial question of pulling data from Company "B" and Platoon "C" and display this data in a report. So what would be the expression in the criteria portion to use in order for this query to work correctly for pulling data from Company "B" and Platoon "C" and display this data in a report in this case? Can you use a select statement in the criteria portion of a query in the query design mode of a query and have the query work right as it's supposed and intended to? Essentially what I'm asking actually would add two queries from two different combo boxes into one query and form a report from this query. Also if the values in either combo box is blank or is null then I want to display all data in that specified Company or those specific Platoon(s). Thanks for your continued support and all that you do!

very respectfully,

EZELL MARTIN

Bradford Roche on 9/14/2016: Where would I find the class on search form using stubforms? Thank you.

Reply from Alex Hedley:

Is there a reason you can't have the searching in the sub-form itself?

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP