Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
Access Search Seminar
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   9 years ago

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

Filter Combo Box Upload Images   Link 
Ina van Rooyen 
40 hours ago
Here's another one...
I have a table with almost 600 dogs in total.  I have a Status field and each dog has a status, eg. Breeding, Puppy, Sold, Retired, etc.
The dogs I deal with on a day to day basis are mostly the Breeding dogs. Read More...
Adam Schwanz
36 hours ago
Sure you could use an after update event on your status field to change the row source of your dog name combo box. Add a where clause to it that filters it by the value in the status box.
Add a Reply
Search with Combo Box Upload Images   Link 
Ina van Rooyen 
2 days ago
Hi Richard
I have three combo boxes on a continuous form with approximately 600 records in total. I use the exact same code you used in the Genealogy seminar with different field names, obviously. Read More...
Richard Rost
2 days ago
Your code looks solid. Usually this problem is because people forget the space before "ORDER BY" but you've got that covered at the end of the WhereStr. Only time you'd have a problem would be if there was no WhereStr. So you might want to add a space in there. Let's see what your final SQL statement looks like. MsgBox it and post a screen shot.
Kevin Robertson
2 days ago
What is the "& wh" at the end of sql?
Your WhereStr appears to go straight after with no space.
Add a Reply
SearchF on multiple tables Upload Images   Link 
Elaine Heltman 
45 days ago
I created a Search form with unbound search fields from 4 different tables and with results appearing in 4 unbound list boxes. Works like a dream. Id like some advice on the proper VBA to add to the Search button On-Click event for a Msgbox to appear when an entry is not found for only the search field containing the search text. In my most recent version, the msgbox runs even when a search result is found and if I search using the other search fields - it's indiscriminate. Ive tried a few variations and searched your website for ideas; here is my most recent attempt: Read More...
Kevin Robertson
45 days ago
Try this:

If Forms!SearchF!LesseeList.ListCount = 0 Then
   MsgBox ("Search entry not found.")
End If
Elaine Heltman
45 days ago
Thanks for the quick response, but it didn't work. The MsgBox still runs if a result is returned.
Add a Reply
Search Seminar 34 Reports Upload Images   Link 
Stephen Southcombe 
2 months ago
As requested I am transferring this question from my post on Facebook -

"Richard, God bless ya, but I am totally not getting Lesson 34 of the Search Seminar.
I have all this vba allowing me to search a large table just about any way anyone could want and view it in a form.....but I can't translate that search method into a report? Read More...
Richard Rost
2 months ago
Hi Stephen. I'm not sure what you don't understand. Explain to me the problem, please. Do you need more help understanding string concatenation?
Stephen Southcombe
2 months ago
Hello Richard;

No, I'm having trouble understanding / programming my reports to show the data I want.

I have a complex search form with more than 30 different combo box fields for which I have vba written to look up data according to the values selected (additionally with tri-state conditions). Everything works great for showing the desired data in a form when I run the vba.

My trouble lies in trying to translate that into a report.

I am able to get a "start date" and an "end date" to work together for a report. But when I go beyond that I start getting parameter query boxes popping up on the screen asking me for values that it should be getting from the search form.

I know you state in the Search Seminar 34 Lesson that reports don't function the same as forms and then you go into creating "Wh2" as a means to work around this but I am not understanding how to code it.

Add a Reply
Combo Box Search not working Upload Images   Link 
Julian Okolonji 
2 months ago
Hi Richard, I have followed the search seminar and I cant get the combo box on customer form to work, does it have anything to do with the version of access (O365)

Many thanks
Adam Schwanz
2 months ago
Combo box search works fine on o365. What error are you getting? Rewatch and check yours.
Richard Rost
2 months ago
Combo boxes haven't changed much in years. Try a new blank database. Also check the Troubleshooter.
Add a Reply
Search Field about anything Upload Images   Link 
Emad Al-Baghli 
3 months ago
Hi, if I have table has a lot of fields, and I want to put criteria that search anything in that table how can I do it plz?
Juan C Rivera
3 months ago
Hi Emad, try looking at the tech help video Richard has several videos on single filed and multi field as well as set up filters.

Adam Schwanz
3 months ago
This is covered in the search seminar as well. I believe lesson 30.
Add a Reply
text box filter combo box Upload Images   Link 
Emad Al-Baghli 
3 months ago
Hi, I have combo box a lot of Names and I want to put text box next to it that if I type any letter in the text box it will filter this combo box to make it faster to pick

kindly, can you change my name to "EMD" I do not want my full name show up :) in forums and conversations public plz
Adam Schwanz
3 months ago
What you need to do is make an after update event in your text box to set the row source of your combo box. Then use the WHERE to filter based on your text box. Something like

ComboName = "SELECT ID From TableT WHERE ID Like """ & TextBoxName & "*"""
Adam Schwanz
3 months ago
Forgot to add ComboName.RowSource not just ComboName
Add a Reply
Inner Join Upload Images   Link 
Joseph O'Malley 
4 months ago
Hi I am just having a few issues joining my tables when writing the SQL = portion of the code. I am trying to inner join 3 tables which correspond to the data I want in the list box.  

Any help would be greatly appreciated.
Joseph O'Malley
4 months ago
Right now the whole "From"  portion is highlighted in red with some kind of error
Adam Schwanz
4 months ago
I haven't done much with the inner joins so may be way off, but this doesn't look right to me: Where ' wh

I think you need a "&" there.
    SQL = "SELECT PartID, PartNum, Vendor FROM PartsT WHERE " & Wh
    PartList.RowSource = SQL
Add a Reply
Custom Filter Upload Images   Link 
Emad Al-Baghli 
5 months ago
If I have a table, and I make custom filter such as I want the customer to be shown this week or this month, How can I save it and put it inside a button and the user can use this button?
In Excel I can record Macro and then put it inside the button. but in Access I could not do you have any such way to do this?
Richard Rost
5 months ago
That's what queries are for. :)
Emad Al-Baghli
5 months ago
I know it is hard for me to explain as international student :). for example in Excel I use record macro to record my steps, I filter the table and then I stop the macro recorder, and then if I edit the macro it will show me the steps that I have done. I can take that Macro and embed  it with button and this button can be used by users. If I make a query I do not know the exact criteria for example show me this months I do not know how to make it in criteria, is there a such way to show me my steps and then take those steps and then embed it to a button?
Add a Reply
Search Question Upload Images   Link 
Adam Schwanz 
9 months ago
Hello Rick, I went through most of the classes here but still haven't fixed my problem I asked about I believe back in an expert class. Where Search As You Type won't let you put spaces in, have you had any ideas about how to fix that if it's possible? Otherwise I may just abandon the search as you type to let them put more words in. Read More...
Richard Rost
9 months ago
I did resolve the "search as you type" problem in the Search Seminar, Lesson 33, which I see you've taken. Is it not working for you?

If you want to work with numeric values, I'd suggest putting a combo box before the search form that has ">", "<", "=", and "<>" as options. Default it to "=". Then you can add this to your search string accordingly.

Not sure if I covered that last one in the seminar, but it's not hard to implement. If you can't figure it out let me know and I can make a TechHelp video about it.
Adam Schwanz
9 months ago
Thanks Rick, I was skimming through that one since it looked similar to the expert class and I missed the couple minutes of discussing the space error, that's fixed now. Thank you!

You do discuss the symbols in a combo box in your seminar, I just wasn't sure if there was a way to let the user type it in and have it work like checking what ASCII value the first character was or something and applying it to the SQL statement then if it was "" or "".

Thank you
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

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

7/24/2021Missing Months
7/24/2021Center Vertically
7/22/2021Buy Access
7/21/202164-Bit Access
7/18/2021Splash Screen
7/11/2021Access Developer 30
7/11/2021Access Developer 31
7/11/2021Access Developer 31 Lessons

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access search seminar sort filter sql vba  Page Tag: whatsnew  PermaLink