Microsoft Access Multi-Field Search Form
ฝัง
- เผยแพร่เมื่อ 22 ก.ค. 2024
- NEW!!! See the updated version 2.0 video: 599cd.com/SearchForm2
Learn how to build a multi-field search form for your Microsoft Access database. We will start with a simple table containing customers. We will then build an unbound form with text boxes on it to allow the user to enter in search criteria. For example, you can see customers with a last name of "smith" who are from "buffalo". Finally, we will build a query using Forms!FormName!FieldName notation to read in the search criteria. We will use the LIKE keyword with wildcard characters so that any search fields left blank are ignored.
For more information on this topic, visit 599cd.com/LMZZ
Get on my Mailing List: 599cd.com/YTML
![The Wall Song ร้องข้ามกำแพง | EP.202 | ตั้ม วราวุธ / เบสท์ คำสิงห์ | 18 ก.ค. 67 [1/5]](http://i.ytimg.com/vi/cwIoA7XHqnE/mqdefault.jpg)
Thank you! I haven't tried it yet BUT having trawled through the Internet again and again looking for form access multiple fields and only finding complicated solutions, I added the search criteria "simple" OR "easy" and yours was the first result. Bingo! Thank you again from England.
Hope it works for you.
I used this video along with a database I made to figure out which SIM cards go into what smartphones based on Manufacturer/Model. I was wrecking my head all day trying to find a way to do this and this video was PERFECT. Thank you so much!!
Welcome
You're absolutely right. Again, thanks for taking the time to share your valuable input. I try to impress the "this is a quick tip, see my full tutorial for lots more information" message in these short tutorials, but you're definitely right that this technique is only for the simplest of uses. On a side note, I just want to say that I love your books. Your Access 2010 Inside Out and Building Access Applications books are two of my favorite Access books - and I have a LOT. :)
I watch this video in 2019 and express my deep gratitude. You really helped me. With love from Armenia!
My pleasure... from Florida in 2021. :)
You are correct, John. Thanks for sharing. Obviously if you have much larger sets of data, you need to adjust your approach. This is only a brief tutorial for "novice to intermediate" users. In my full seminar I go over more advanced techniques, such as writing custom SQL statements in VBA to get exactly what you want.
I did figure out a solution, but not in query form. My solution was to add a default value to each searchable field on the form I use to input information. Each searchable field is a simple text box. In design view, I opened the property sheet, and clicked on the text box. In the Data tab, there is an option for a default value. I use "-" as my default value, so that when I don't have information for a particular field, there is at least something to show the field is not null. Works great!
This is such a great tutorial! Every single one of your tutorials are absolutely FANTASTIC!
And still are, with it now being Saturday, September 18, 2021. 👍😎
Thank you, from 2025. Oh wait... I set the DeLorean for the wrong coordinates again... hang on...
You are correct. Looking for blank values gets a LOT more complicated. I'm addressing this issue in my upcoming Access Search Seminar which should be out later this week. You basically have to use IIF and IsNull functions to handle this.
Love them. In fact, Inside Out is one of the books I routinely recommend to my customers who ask for a great reference book to keep on the shelf for Access developers.
Thank you! Self taught in everything I use at work but many years in the past. Hadn't built a DB our a query in about 4 years and this was the best refresher I could have used.
What can I say Mate. You're the best!!! I'm wrapped to get your response and attempt to assist me. I did actually follow your lesson from my PC with my laptop doing the work... However, I will try building your data base and see if I can work it out from there. Again, THANKS!!! Your lessons are truly inspiring and assist people who would not otherwise have the support to get their head around Access. Cheers.
If the table structure is identical (or at least close enough) you could UNION them together and just search one resulting query. There are other, more advanced methods you can use that involve some SQL and VBA. I'll be covering these in my upcoming Access Searching & Sorting Seminar which should be available next week on my web site.
Excellent tutorial, in 5 minutes i had the Search Form/Query running...thanks a lot
Welcome
That bit starting at 5:45 helped me a lot. Coming from T-SQL and MySQL, Access is a bit fuzzy with operators.
It can be confusing if you're used to multiple versions of SQL. It got me too at first.
It's impossible for me to tell you what you've got wrong without seeing your database. Try starting again from the beginning, and build the database EXACTLY as I build it in the video.
I would have to see your database to tell you what you're doing wrong, however try building the exact database that I've built in the video - starting from scratch. Follow my steps EXACTLY. This might help you to figure out what's different in your database.
Sure. Just feed to search results into a blank form with a list box, then have an On Double Click event for that list box to open up the record in your form. I cover this in detail in my forthcoming Access Search Seminar (which should be out this weekend).
I want to create a search form that also includes searching between two dates. Also after following this video, my name search does not filter, but selects all names. Love your tutorials.
Your videos are always top notch! I love to watch them for I learn new techniques every time. keep it up. Thanks John
You're welcome. 3 years later. :)
First, if you're a novice, watch my complete 2.5 HOUR beginner Access tutorial (right here on TH-cam). When you type data into a TABLE or a FORM that's BOUND TO a table, that data should stay there. You don't have to do anything special to it. Usually when people tell me this is happening, they have an UNBOUND FORM, which means a form that's not linked to a table. Watch my beginner tutorial for instructions on how to properly set up a form.
I wish you were an instructor at my college, you made this so easy
This can be tricky with multiple fields. Sometimes even wildcard searches don't work with NULL values. I'll be covering this in depth in my upcoming Access Searching & Sorting Seminar... and I'll try to post a tip video about this on my Channel.
You can use the IsNull function or Is Null in a query criteria to find those blank records and enter the data.
It finally worked! I failed so many times last night! So glad it finally worked lol! Thanks a lot!
Awesome video, very straight forward and no need for macro. Thank you so much :)
YW
Great tutorial; I'm having difficulty implementing a multi-field search form using several tables with many-to-many relationships and lookups. For instance, I have one table listing foreign students, a second table listing domestic students, and a third table with classes, as well as junction tables between the class table and the two student tables. The objective of the query/form is to determine which classes two students (one foreign, one domestic) share.
Thank you so much for these Tutorial. It solved what I was looking for.
My pleasure.
varchar? Is your backend table stored on SQL Server? In Access you would just use a Yes/No field.
You an submit it via the HELP link on my web site. TH-cam doesn't allow links in comments, but see the MORE INFO link in the video above.
thank you so much. I'm a student and I have to build an application and one of the pages of this application will be a multi-field search form - I knew how to program it but the way I knew it was not really suitable for more than 2 fields (check if both are empty; then check if both are filled in; then check if one of them is filled in) so this is really handy. I'm using a newer version of access but I'm sure this will still be usable. thanks!
Welcome
All of my more advanced lessons are available on my web site. You can watch them right in your browser, and stay in India. 599cd(dot)com
You're very welcome. Let me know how it goes.
Sure. You could display the results in a list box or subform.
Very specific, complete, well explained and useful video, thank you so much!
You're very welcome!
Hey Richard, when I split my database the remote computers forms (used form wizard) that link to my tables on a server open blank in form view but they populate in design view. The forms that I created that links to my queries (used design view) work fine. Why is this? I can access my tables over the network.
Thanks for the video, it has helped me a lot.
I'm not getting empty fields to show up in queries though.
Like "*" & [Forms]![Job Site Search]![CityF] & "*"
That combined with the other 3 fields I'm using should return any search criteria where there are empty fields? Yet all I see when I hit my 'run query' button is all records that are filled in completely.
I've watched your video 3 times and been to your website as well. There doesn't appear to be anything else on the web for this either.
Subbbed! thanks for this tutorial sir, i changed the query command to REQUERY so that the query table is updated on the same form =) just what i needed, thanks again!
Great tutorial.
But when i set in all the unbound field, the criteria with like "*" & [forms]![zoeken]![name] & "*", it doesn't give any results.
It only works when i set the like in 1 field criteria, not in all criteria.
Any suggestion.
Guido
Hi, can I ask. how do you create a search form that is popped up. my search form is worksheet style :(
This is brilliant I do have a question however. When using this to search through a table with lookup fields it doesn't seem to recognise them, only works with pure text entries. Anyway I can modify this to recognise the drop down list choices as well as search criteria? Thanks for a great tutorial!
hey there, i have tried this and it works great... until i went to add to more data and the new stuff didn't show up when i searched... i have the search boxes in a form, which is then referencing the query and displaying results in a subform. my search button has the requery function aimed at the subform.
i'm also looking to be able to select an entry and have a button to manipulate that information further
got any suggestions?
Thanks. That's the tutorial i was searching for. Well and quickly explained
Very happy to hear you like my books!
Turn your control wizards on (option in the controls toolbox)
hi Sir. Ive an inquiry about a form in msaccess with splashform. im trying to insert a command button in the main form that opens another form but it doesnt open that form. any idea sir? thanks.
Very nice presentation style - not too wordy or detailed and not too quick. thanks
You're welcome.
Thanks, you saved my college life :D
Love it! Thank you. Just one question, how would you be able to create a query that can search through different tables all at the same time? I was trying to figure it out but wasn't able to.
Hope you can help! Thanks!
That all depends. Are those tables related? If so, join them together. If not, you might be able to Union them together.
599cd.com/Relationships
599cd.com/Union
Thanks for the video! This is great but I have one minor issue...if a field is null, it will not return that record. Is there a way to modify the query to include if a field has a null value?
When I run the query once, it changes the design of the query and when I try to run it again (from the Form), it no longer works. Do you know why this would happen?
This video is the best video ever written for Access. I followed the instructions step-by-step however I am still having an issue with my query. When I search, no results are given. I know I have something out of place...I just don't know what. How can I troubleshoot and fix?
Hi there. I input the formula exactly as you specified but using my own search form and field so it reads: Like "*" & [Forms]![ChemSearch]![Chemical] & "*"
However, everytime I run the query, new data is not shown and if I open the query it comes up with "Enter Parameter Value. Forms!ChemSearch!Chemical!".
Hi - I'm referring to fields that use look-up tables and I keep getting their ID number instead of the text of the field - I know there's a simple fix and I've done it before - but it's been a while and I can't remember how to remedy this relationship issue so that my combo boxes in the SearchForm return the text category rather than their IDs...
thank you very much!!! amazing simple and nicely described!!!
AMAZING!!!
Thank you so much for sharing! This will make my job so much easier :D
My pleasure
Well, I wouldn't put foreign and domestic students in two separate tables. I try to keep LIKE DATA together in the same table. If you want, use a checkbox (or a country field) to determine if they're domestic or not.
Richard, Is there any way to include a date search in the multi-field search form???
Just base a form on the results query. I cover this in my full seminar.
My table has male/female as varchar in one of the columns. Now, if I want to see only male records, I'll get the female records too, as it has 'male' in it. To eliminate this, I have tried using iif in the criteria field. But I face another problem where I need to check if my text field on the form has a 0 character string or a null value. It's getting complicated. Can you please suggest an easier way to do this? I'm using a lot of drop down options, check boxes, etc. on my search form
Just as an update, I've tried every type of wild card syntax in the query criteria that I can think of and that the system accepts, and none of them work. Is there a setting in MS Access in 2010 that I need to be aware of?
Hi, did you solve this? I had the exact same problem, came up with a query statement to make a workaround. I ended up using lots of brackets and OR statements. Here's an example of a parameter query selection from a form (called Frm_Report_Query_Builder):
Like "*" & Forms!Frm_Report_Query_Builder!Title & "*")) Or ((YourTableName.Title Is Null) And (Forms!Frm_Report_Query_Builder!Title=""
Solved the problem of not returning results for nulls. Hope it helps.
I have been working on this particular search form, however when I replicated exactly what you had at 5:15 but my results kept showing up blank. Even when I did enter something into the search form. I do not understand why.
Hi. I have followed the instructions on this tutorial but for some reason every time I run the query it comes up blank. This is the script I have used: Like "*" & [Forms]![F_Search]![First name] & "*"
where F_Search is the name of my form and I have named the text box "First name". I have no idea why this is not working! Can you please shed some light on this?
Incredibly helpful tutorial. Is there a way to search for more than one name? For example, I type Joe and Smith in Search and it outputs both Joe and Smith results. Thanks!
hi, im using access 2007, i copied this tutorial as an exercise and i'm wondering why my query table wont refresh like yours do,
whenever i hit the "Run Query" Button i have to hit Refresh to see the latest result on the search that i typed in my form, mine does not automatically display the new values like yours do
hope you could help me - thanks!
What if some fields are blank in the search - it appears that there is no response for that row. Looks like all rows must have data in the search fields to get a response for that row?
Hey, Great vid. Is it possible to run a search across multiple fields but only have one search box. An example from your video would be having a single search box, I type in "mi" and run the query. the query would return rows 1,2,4 and 6 as each of these have "mi" in one of the fields (Smith, Mike, Miami). Thanks
Hi, I totally love your lessons and have learnt so much from them. However, I did everything (numerous attempts) as stated, however, despite using specific words used in the query, the 'run query' merely returned ALL fields in the resultant table. Can anyone tell me where I'm going wrong here? As I've been over and over this, I would be most grateful for some positive direction. Kindest regards
This almost does what I am trying to do expect I'd like the search result to show up in a form instead of a table, any easy way to do this?
Or show up in a table in the form below in like a list and then select one to show up in a form
is it the same if you replace the textboxes with a drop down list?
I need to make a form with text boxes that can be clicked to enter a new customer (user is directed to Customer Table) or submit a work request, but I'm so lost. Can you help?
I cover creating a much more complex search form using dynamic SQL in my full Search Seminar.
Hi, when I run my query from the form, it only shows the first row of data for that criteria.
For example, I want to show me all the Joe's in the data table, but it only shows me the first Joe, and the first Susie, etc etc.
Everything is spelled correctly, I've checked 100X's.
THANKS!
Are you using actual Date fields? If so, they behave a lot differently than text. You have to enclose dates in # symbols, like this: #1/1/2012#
Can you point me to the tutorial where I can setup the query results to show as a report or form?
Hi, I've literally tried this over and over in my database and followed every instruction to the T. However, every time that I run the query, it returns ALL of the entries and not just the ones that match. I have absolutely no idea why it is doing that. Any help is appreciated.
Thanks
hello sir, i have a question, can i possibly display the query result below the search form? i think it would be great to see the query result on the same form after hitting the run query button, thanks!
I have watched all your tutorials and think there fantastic and my boss thinks my database is fantastic so far. I'm just trying to tweak a few things. As for my previous question to you I have figured it out on my own. If I left one of the fields empty on original table it didn't bring up the whole record. Just trying to understand if I leave all search commands empty it brings up everything hut if a filed is empty it omits it.
i just want to know if it is possible when you make a database, when you put a data in your database it will automatically separate data. what i mean is for example you have 4 categories age 1-17, 18-30, 31-59, 60 and above.. then it will goes to there respective age categories. sorry i'm novice only in ms access.
I had a similar problem. Everything was spelled correctly, but the query with the criteria EXACTLY as specified returned all records. Any suggestions?
My form works and the query populates. YEAAAAH! But the form remains on top of the query results. How can I make the form window close once the button has run the query?
I joined the queries in relationships they all have a Primary key 'EEID'
a number. This still didn't allow me to due a report in the report wizard.
Exactly what i was looking for, Thank you so much for the guidance, However I ran into a problem, when I make a navigation form and insert mine into it i get a error parameter.
599cd.com/EPV
Thank you. Useful for what im working on.
THANK YOU!! You are a life-saver!!
WElcome
This is a clear and easy to follow tutorial. Question. If one of the fields I want to search on is a date field (DD/MM/YYY), and I want to have on this form where you enter the year of the date only (YYY). How do I code that? I just want the user to type in the 4 digit year.
Use the Year function and create a separate calculated field. 599cd.com/Calculated
Very helpful, thank you.
welcome
I'm trying to add a text field in which people have to type something, and accordingly a button which opens up a form/record of one person in the database.
Yet, whenever I try to create the button, the value that people enter doesn't appear in the list of fields in my create button wizard, why not?
Hello is there a way to make a search with criteria Firstname and Last, City, State Info appear into the SearchF and not pop up the SerachQ, because im trying to hide all the database and just let the SearchF like an App infront of the desk
I learn more than about your teach-thanks a lot
I'm having a problem with my form that I can't figure out. I'm using your tutorials to edit and build my database. I have part numbers I'm searching with descriptions but for some part numbers it can't find the items. For example it can find number 1590 but not 1629. I figured there's a typo but I can't find anything wrong. Any ideas of where I should look?
That's weird. Without seeing what you've done, it's impossible for me to help you. Try the new video's technique: 599cd.com/Search2
HI,
HOW CAN WE SEARCH THE DATA OR FILTER THE DATA IN BETWEEN TWO DATES ON THE SAME FORM WITH TEXT BOX.
thank you for making it sooo simple!
Thanks alot! it really works when i redo everything.
I am at about 7:45 in the video, after we create our 'run query' button. Before we run another search, do we need to close out the existing query everytime? If I don't, it simply brings me to the previous query ran with that criteria, not the new criteria I specified. Thanks.
Yes, close the query.
thanx for the helpful video, I have a little question, I have Access 2010, I followed your tutorial step by step, but I stock in 7:13 , when I inserted the command button it didn't show me the window where I can choose the categories and Actions, would you please advise, thank you in advance :)
I follow your instruction but after one search the query dont want to take a second search. Can you tell me what may be the problem?
Hi,
I would like to know where I can find level 2 becouse I already end 1° level
I did this an the proplem I am running into is if a field was left blank out of my choices it is not returning the entire record then.. Example I have Location, name , Phone Number, Municipality. If the phone number is blank it is not returning that record at all.. Any idea why?
Yes, you could certainly make a query to group data like this.