Microsoft Access Multi-Field Search Form

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

[@guyosborn615]

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.

[@599CD]

Hope it works for you.

[@pyrokinetic666]

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

[@599CD]

Welcome

[@599CD]

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

[@DavidDavidov1702]

I watch this video in 2019 and express my deep gratitude. You really helped me. With love from Armenia!

[@599CD]

My pleasure... from Florida in 2021. :)

[@sandrahebner8561]

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.

[@599CD]

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.

[@MargieDuerr]

This is such a great tutorial! Every single one of your tutorials are absolutely FANTASTIC!

[@cruzinsweetsntreats]

And still are, with it now being Saturday, September 18, 2021. 👍😎

[@599CD]

Thank you, from 2025. Oh wait... I set the DeLorean for the wrong coordinates again... hang on...

[@TCSura]

Excellent tutorial, in 5 minutes i had the Search Form/Query running...thanks a lot

[@599CD]

Welcome

[@Renderceg]

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.

[@johndavy3820]

Your videos are always top notch! I love to watch them for I learn new techniques every time. keep it up. Thanks John

[@599CD]

You're welcome. 3 years later. :)

[@599CD]

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.

[@edabano77]

Thank you so much for these Tutorial. It solved what I was looking for.

[@599CD]

My pleasure.

[@lindsayalden9900]

Awesome video, very straight forward and no need for macro. Thank you so much :)

[@599CD]

YW

[@599CD]

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.

[@TexanInParis06]

Very happy to hear you like my books!

[@GAVVVR]

Thanks. That's the tutorial i was searching for. Well and quickly explained

[@danielzamarron6993]

Very specific, complete, well explained and useful video, thank you so much!

[@599CD]

You're very welcome!

[@brianlee6639]

That bit starting at 5:45 helped me a lot. Coming from T-SQL and MySQL, Access is a bit fuzzy with operators.

[@599CD]

It can be confusing if you're used to multiple versions of SQL. It got me too at first.

[@whoyathought400]

It finally worked! I failed so many times last night! So glad it finally worked lol! Thanks a lot!

[@britesynth]

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!

[@javadrajabi9639]

I learn more than about your teach-thanks a lot

[@yamahajt]

I wish you were an instructor at my college, you made this so easy

[@007theleo]

thank you very much!!! amazing simple and nicely described!!!

[@deezlittlethingz]

AMAZING!!! Thank you so much for sharing! This will make my job so much easier :D

[@599CD]

My pleasure

[@MrMayes69]

THANK YOU!! You are a life-saver!!

[@599CD]

WElcome

[@599CD]

You're very welcome. Let me know how it goes.

[@minerdalta]

Thanks, you saved my college life :D

[@nebbie03]

thank you for making it sooo simple!

[@599CD]

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.

[@harperassociates]

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!

[@argiris2011]

Just perfect!!!!! God Bless you friend!!!!

[@599CD]

Thanks

[@gisontherun]

Thanks man, helped me a lot!

[@jbbauer0]

Thank you. Useful for what im working on.

[@masterblasterrs]

great tut, Helped me out!

[@iseenochains-oops7863]

Very nice presentation style - not too wordy or detailed and not too quick. thanks

[@599CD]

You're welcome.

[@lisacalhoun306]

Fantastic tutorial! Thanks!!

[@599CD]

My pleasure.

[@styloroc2000]

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.

[@tommyleemac]

Exactly what i needed thanks!!!

[@599CD]

You're welcome.

[@599CD]

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

[@form4b]

Thanks alot! it really works when i redo everything.

[@599CD]

Yes, you could certainly make a query to group data like this.

[@mobilexcellence]

Very helpful, thank you.

[@599CD]

welcome

[@ncking58]

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.

[@vendag865]

Brilliant sir! Thank you very much!

[@599CD]

Welcome

[@aedegroot94]

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!

[@599CD]

Welcome

[@xm_tech]

Very Helpful, many thanks.

[@599CD]

Welcome

[@BOWENBRIGSD101]

great video very clear and helpful thank you

[@mikeattwood2788]

Great guide. Thank you

[@599CD]

You're welcome

[@gerfer6261]

Thanks- Handy, Helpful & Happy

[@599CD]

You can use the IsNull function or Is Null in a query criteria to find those blank records and enter the data.

[@nandanatennakoon6976]

This video was really helpful me. thank's too mutch for you..!

[@599CD]

You're welcome.

[@RossR692]

this was great thanks!!

[@599CD]

Welcome

[@599CD]

Sure. You could display the results in a list box or subform.

[@civicchic79]

This is a GREAT way to create a search form versus using drop down boxes or DLOOKUP!!!! Thanks!!!

[@vonejon5643]

I really appreciate your hard work

[@599CD]

Thanks

[@novilasyanto7793]

This tutorial is good, and easier to understand

[@yustikarani1500]

Great lesson. You help me so much

[@599CD]

My pleasure

[@DeeBooTV]

THANK YOU SO MUCH SIR. ITS VERY HELPFUL

[@599CD]

My pleasure.

[@homsi2468]

Thank you . this was very useful

[@599CD]

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.

[@MrNotubo]

Wow tnx for the tutoriaL!!!

[@LoveliBrowne]

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?

[@kelevra1493]

Thanks, this video realy help me!

[@piratesmvp]

Thanks this helped a lot

[@moodkn]

Oh Thanks! Very helpful

[@599CD]

My pleasure.

[@lfscat]

THANK YOU THANK YOU THANK YOU!!!

[@599CD]

Turn your control wizards on (option in the controls toolbox)

[@599CD]

Just base a form on the results query. I cover this in my full seminar.

[@obsessivelyShayz]

thanks! This was heaps helpful (:

[@NS2Las]

wow! thank you! who knew .. thanks for sharing...

[@599CD]

Welcome

[@599CD]

I cover creating a much more complex search form using dynamic SQL in my full Search Seminar.

[@donalddouglas]

Good stuff - thanks!

[@599CD]

You're welcome!

[@sumitpathania5188]

TY, great job

[@raynealeka3950]

Invaluable Video!

[@599CD]

thanks

[@venkatyalamati3285]

Amazing tutorial sir... Thank you

[@599CD]

Welcome!

[@599CD]

varchar? Is your backend table stored on SQL Server? In Access you would just use a Yes/No field.

[@chelseawilkinson307]

You are wonderful!

[@599CD]

Thanks

[@sgtzsquad]

Thanks for a great video

[@599CD]

You're welcome

[@jamesdavebautista1099]

thank you for this. i passed my project

[@599CD]

My pleasure

[@MrTimdery]

To have a good command in MS Access, one has to look this tuto. It is wellcome

[@599CD]

Thanks

[@DanielGam89]

Thank you Sir!

[@599CD]

welcome

[@lesliegarrett]

Your Better Than My Dad's Tutorials, And He's Been At This For Years...

[@pespes1459]

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.

[@daudacademy24]

Very helpful, Thanks a lot....

[@599CD]

Most welcome!

[@josencreative2242]

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!

[@599CD]

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

[@599CD]

It could be several things. I'd need to see your database to tell you for sure.

[@habetful]

THANKS!

[@mrmuj4039]

Thank you!

[@599CD]

You're welcome!

[@Larwai]

Hello: Thank you much helpful.

[@599CD]

You're welcome!

[@MrDangles66]

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]

599cd.com/EPV

[@SprikSprak]

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!

[@musarratzaidi685]

Good help, impressed

[@599CD]

My pleasure

[@599CD]

All you have to do is build the query and then use that query as the record source for the report or form.

[@599CD]

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.

[@jossurbab]

Thank You So much. I am an ameture. I got the hang of Access.

[@599CD]

My pleasure.

[@599CD]

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.

[@Rocky-rk4df]

Perfect. Thanks.

[@599CD]

You're welcome!

[@MulticamAustralia]

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.

[@599CD]

Use the Year function and create a separate calculated field. 599cd.com/Calculated

[@599CD]

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.

[@user-hl2um8vh3w]

Thank you very much

[@599CD]

You are welcome