How to Make a Multi-Term Search Form and Report in MS Access
ฝัง
- เผยแพร่เมื่อ 22 ส.ค. 2022
- In this episode, we're going to make a form that has several free-form text controls on it that will narrow our search on a single text file so that we can get records matching several phrases. This is handy for situations where you have a text field with descriptions in it that could be in different order, or using plain language where our search items might be embedded in a sentence. This episode will show you how to use the Like operator in Access while accounting for apostrophes in names like O'Connor, so that you don't get syntax errors.
Related Videos:
How to Make a Search Form with Combo Boxes in MS Access
• How to Make a Search F...
How to Make Cascading Combo Boxes in MS Access
• How to Make Cascading ...
Bound vs. Unbound Forms and Controls in Microsoft Access
• Bound vs. Unbound Form...
How to Make a Listbox in MS Access Part 2: Multi-select
• How to Make a Listbox ...
How to Create In and Out Listboxes in Microsoft Access
• How to Create In and O...
How to Make a Multi-Term Search Form and Report in MS Access
You are watching this video now!
How to Use Timers to Run Actions or Code on an Interval in Microsoft Access
• How to Use Timers to R...
How to Use Before Update in Microsoft Access
• How to Use Before Upda...
How to Make Changes to Text in a Textbox in Microsoft Access Using SelStart and SelText
• How to Make Changes to...
How to Add Combo Box Rows On-The-Fly in MS Access
• How to Add Combo Box R...
How to Run Code as Your MS Access Form Opens - On Open, On Load, and On Current
• How to Run Code as You...
How to Use On Close and On Unload with Microsoft Access Forms
• How to Use On Close an...
Join me on Patreon!
/ mackenziedataengineering
Want the code from this video?
mackenziemackenzie.com/downloads
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#msaccess #vba #dataengineering
i really like the way you start video " hi, I am your host Sean MacKenzie". and rest is outstanding ... helped👍 me a lot..
Thanks! 😃
Great video! Access Form has on navigation buttons little search field, but it is small. In your future video, can you show how to create text box with same functionality to search entire form and highlight what has found from VBA?
Great suggestion! I'll add that to my list.
By the way, love this! For my purpose MS Access works better than an Excel spreadsheet. The GUI interface provided in forms and recordsets is greatly underappreciated in my opinion. I get frustrated with Microsoft for slowing further developing the software. I know they want to push Teams, and in many ways legacy Access does overlap in some ways with Dataverse, however see they are intentionally stagnating Access's development to push more profitable products. I just dont see as a data wrangler a replacement. it is efficient and Cloud isnt always a good fit.
I agree with your sentiment here. In terms of recent updates, at least they have done some cool work to integrate Access with Dataverse and some Modern Reports (2016) to update from the aging charts that were there before. For apps, I think it is a great platform for developers to develop on. If you can think of it, you can probably create it in Access, and that is the beauty of it. Learn on your desktop, with proper RDBMS techniques and not needing any data servers etc. Learning how to build a cool front-end and how to code. How cool is that?
Specifically for data wrangling though, I often find that it is the power of "RDBMS engine on the desktop" that can give you superpowers and an edge over others; it combines a fast, easy to use query GUI with a workhorse, time-tested scripting language (VBA) and integrated data platform (DAO). I write mostly in other languages and use other databases for most of my projects, but when it comes down to it, in a time-crunch, and when you need to wrangle (or hack and slash) _actually complex_ data accurately for a particular purpose, Access just beats out the competition. Hands down.
I will create a program that will give thanks, respect and appreciation to your person for the ideas and effort you provide .really a lot of thanksSir .
Thank you!
Great tutorial. One question. There is a way to convert this search multi term in one input box? Maybe 3/4/5 term whit space separator is reliable? Sorry for my bad English.
You could definitely do it - if you used a space as the separator, you could do something like:
strSearch = InputBox("Please enter the search terms:")
arSearch = Split(strSearch, " ")
For intTerm = 0 to UBound(arSearch)
strSearch = arSearch(intTerm) 'get each one and do something
'add the search term to your where clause etc.
Next
Very interesting video indeed.
Keep it up
Thanks a lot!
This is phenomenal in how easy you made this to understand. Thank you Sean! FOLLOWUP QUESTION: if i wanted to add under 'SEARCH_FORM', the data itself; would it be best to create fields pointing to the fields being filtered so they are visually updated on screen as each search term is entered or ... ?
You could do that if you like, perhaps putting the selected rows into a Continuous Form subform, or perhaps a Listbox just below the buttons. In this case the Form or RecordSource would just be "Select Title from Titles " & strWhere
Me!lbxTitles.RowSource = "Select Title from Titles " & strWhere
Something like that!
Hi Sean, great Job indeed. My search form gives me one result at a time, yours is more detailed. Nice. will appreciate if you could you share the coding.
Thanks! You can get the code at mackenziemackenzie.com/downloads
Thanks!
Much appreciated! Thank you!
I'm will be in the process of converting Access tables into Dataverse. Do you have any experience with this?
Just exploratory work, but I'm looking to include it in future episodes!
Sean, I'm using this code in a non-commercial database I'm building for my department. We're very strict about proper citation. What are your citation requirements? Thanks. BTW: Great Video, thanks again.
Not really required, but if you like you can Cite Sean MacKenzie Data Engineering with th link. Never really thought of that before! Thanks for asking!
What is the size of the file?
I think it is 151Mb. It includes the sample data over 1m rows.
Please Give this project file
I'll take a look this week and post it!