Microsoft Access Multi-Field Search Form
HTML-код
- Опубликовано: 30 июл 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
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. :)
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 watch this video in 2019 and express my deep gratitude. You really helped me. With love from Armenia!
My pleasure... from Florida in 2021. :)
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
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.
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.
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...
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.
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.
You're very welcome. Let me know how it goes.
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).
Just base a form on the results query. I cover this in my full seminar.
varchar? Is your backend table stored on SQL Server? In Access you would just use a Yes/No field.
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.
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!
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
Turn your control wizards on (option in the controls toolbox)
It finally worked! I failed so many times last night! So glad it finally worked lol! Thanks a lot!
Yes, you could certainly make a query to group data like this.
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. :)
Awesome video, very straight forward and no need for macro. Thank you so much :)
YW
Thank you so much for these Tutorial. It solved what I was looking for.
My pleasure.
Very happy to hear you like my books!
You can use the IsNull function or Is Null in a query criteria to find those blank records and enter the data.
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.
Thanks. That's the tutorial i was searching for. Well and quickly explained
Sure. You could display the results in a list box or subform.
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.
I wish you were an instructor at my college, you made this so easy
I cover creating a much more complex search form using dynamic SQL in my full Search Seminar.
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!
AMAZING!!!
Thank you so much for sharing! This will make my job so much easier :D
My pleasure
Very specific, complete, well explained and useful video, thank you so much!
You're very welcome!
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
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
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.
Thanks, you saved my college life :D
Yes, but you have to enclose dates in # signs, like:
"SomeDateField=#" & MyDateValue & "#"
Sir please show the procedure to make it in the design view.
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.
Hi, can I ask. how do you create a search form that is popped up. my search form is worksheet style :(
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.
Thank you for the useful training. In case one of the text boxes represents a DATE value the query will pick only records with the same date BUT if I add a GREATER or SMALLER sign in the criteria the query will appear blank. In shot: How do I command the following criteria
Like "*" &
You can't use LIKE with an inequality.
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.
thank you very much!!! amazing simple and nicely described!!!
Very nice presentation style - not too wordy or detailed and not too quick. thanks
You're welcome.
Another problema in Access 2013 with Like "*" & [Forms]![FormName]![FormField] & "*" is that it even thought it will accept empty field names in the form, it will not display data with empty fields in the table ...... how can I tell it to include queries or data with null or empty fields in the table...?????
+Abel Martinezky How did you fix this? I noticed also it returns no results when some of the other fields are blank (null). Many thanks for any info you can provide.
Hi, Sorry, hope I´m not to late, I couldn´t find an answer for that problema, so I instructed to just use N/A in empty fields to work around the problem...
Ok thanks!
+Abel MartinezkyYou can, when generating the query, instead of setting up your field as [FieldName], right-click on the field section in Query Design and select 'Build' then enter:Field Name: NZ([TableName].[FieldName],"")This replaces your field data for any null records with a blank (which is different to NULL)The first part is an alias that gets assigned to your temporary field data, so you can give this something meaningful to the user.Then, in your criteria, you can use the usual:Like "*" & [Forms]![frmSearch]![txtFieldName] & "*"
+Abel Martinezky
You can use the nz function in the query to return " " if the field is empty
nz([field], " ")
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
thank you for making it sooo simple!
I hope you are still active here and pay attention to this video. This solution doesn't work for me. Although I'm using Access 2013 so maybe that's the issue. But anyways when I use these commands (the LIKE stuff) it only works if I use it on ONE single column. The second I use it for another it screws everything up and only gives me some of the information. For every column I add the less information is given and I'm not typing anything on the search bar for those extra fields. in fact if I don't type anything at all I should get everything but I don't.
What should I do?
It's hard to tell from this side of the Interwebz
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?
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#
Thanks alot! it really works when i redo everything.
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?
Thank you. Useful for what im working on.
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.
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?
Just a heads up, if one of the fields in your table are blank (null) those rows will not be shown on the query. Just ran into this problem and trying find a way around it
Walrus Jr I had a similar problem. Are you querying more than one table? My problem was that I was and I had not changed the relationship type, so the query was using an INNER JOIN instead of a LEFT OUTER JOIN or RIGHT OUTER JOIN.
Laura T. I'm having the same problem now. If you find a fix, please let us know.
See 599cd.com/SearchForm 2.0
It could be several things. I'd need to see your database to tell you for sure.
Thanks man, helped me a lot!
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...
How do you populate those query result into a form field? In php and mysql, those query result will be able to populate into the field.
See 599CD.com/XHL4AA
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.
Very helpful, thank you.
welcome
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.
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
Can i use a checkbox for each record? ive got a form that displays the type of tires a customor has got mounted under their car ( winter or summer ) now id like to use a sort of checkbox or option box for each customer record what is mounted so i can see in a query wich customers have summer tires and wich customers have winter tires...
If not, what would the best option be in my case?
kuil88 That should work - or use a combo box to describe the type of tire installed.
I got it to work, THANKS for your video`s, they are VERY helpful.
1 question: can you create a search button within a form for searching records with the same criteria as in your query from this video? so when you only type a J you go to the record starting with a J ... instead of showing a report...
I have a search button now but for criteria i need to type in the exact same name/number plate/customer number as in the record...
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!".
great tut, Helped me out!
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.
THANK YOU!! You are a life-saver!!
WElcome
Richard, Is there any way to include a date search in the multi-field search form???
I learn more than about your teach-thanks a lot
THANK YOU SO MUCH SIR. ITS VERY HELPFUL
My pleasure.
this was great thanks!!
Welcome
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?
Can you point me to the tutorial where I can setup the query results to show as a report or form?
Works great and video is awesome but I've found one issue. Keep in mind that I'm running 2007 anyways were searching a set of records if the main form has 4 fields and the search form will look up 2 of these fields all 4 fields have to be filled in or the search form will not find it can we make it so if in the main form one of the fields isn't filled I. It don't care?
I hope I typed that so you understand what I'm talking about
Thanks
I'm very sorry for taking so long to reply to your comment. I wasn't very good at keeping up with the comments 4 years ago, but I've been trying to make an effort to get back to everyone. If you still need help with this problem, please feel free to submit your question on my web site at 599cd.com/TH.
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!
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
You want to search the BEGINNING of the field. How about:
LIKE "John*"
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.
Sure. Just use that query to feed a form. Display the form instead.
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
Just perfect!!!!! God Bless you friend!!!!
Thanks
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.
To have a good command in MS Access, one has to look this tuto. It is wellcome
Thanks
I am getting the dialogue box of "ENTER PARAMETER VALUE" while opening or running the query. How can i solve this?
Late to the party here, but for future peeps. Change the code he uses to Like "*" [Forms]![Search Function]![Name] "*"
599cd.com/EPV
great video very clear and helpful thank you
I've gotten this to run almost perfectly. My issue is this... If I press the button after I have entered text in the search field it works perfectly. If I go back to the form and enter in a new search term and press the button, the query opens, but does not re-run. If I manually run it works fine, but I need it to run automatically.
Does anyone have an idea what I missed?
Any help would be appreciated.
I'm having this issue too. Did you ever find a solution?
Same here. Every find this out?
On open of the report or query you can set the OnOpen property to requery, that should do it.
Try in form's design view, on the search button - Event - On Click - select ellipsis to see code that opens the search query and after that add "Requery" with no Control Name.
@@stewartmorrison5488 thanks buddy worked like a charm
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?
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?
is it the same if you replace the textboxes with a drop down list?
i made the same , but when i click on the form , it prompts for parameter value i don't want that .. i want the form to show empty and when i click search i want it to populate the data ..
599cd.com/EPV - check your spelling
Can this be done for a multi-valued column/record? I have a record that has multiple units in it because a shareholder may own multiple apartments or two people may live in one apartment. How do I search for that column? I am using a search function, but it gives me a runtime error saying that I cannot search for a multi-valued field.
Nope. I hate, hate, HATE multi-valued fields. I refuse to use them. They're an abomination to all that is just and righteous in Access. Seriously, you shouldn't use them. Use a second related table.
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!
Works great if all fields have values in the database. If there are some null values the search results are blank even though some of the data exists in the table. So if you Search for Joe but there was no last name entered in the db it won't find the record entered for Joe. Obviously your example should not have any null value fields but I have some records which will have nulls. Any help would be appreciated. Also are you going to be releasing and 2016 Dvds?
I've got an update for this video coming out soon, and yes, I've got a whole bunch of newer lessons since you wrote this: 599cd.com/ACB1
This tutorial is good, and easier to understand
Great video and really helpful! I've created my search form and it's currently working well with 8 search fields. The issue I'm having is that I want to put 10 search fields in my form and when I put the additional 2 Like "*" & [Forms]![FormName]![FormField] & "*" in my query, it doesn't return the correct data if any at all. Does anyone know if there a max number that can be used in one query or perhaps a better way of doing this?
The max statement length, I believe, is 255 char.
Thanks- Handy, Helpful & Happy