Microsoft Access Multi-Field Search Form

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

[@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.

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

[@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.

[@599CD]

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

[@599CD]

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

[@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.

[@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.

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

[@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.

[@599CD]

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

[@599CD]

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

[@599CD]

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#

[@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.

[@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.

[@markromano5743]

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.

[@599CD]

Yes, close the query.

[@AlexPotthast]

Richard, Is there any way to include a date search in the multi-field search form???

[@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?

[@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.

[@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.

[@baffledbumblebee]

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

[@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.

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

[@johnathanaitken5555]

I second what bbmak0 said below... How can you use what you show in this video and use it to populate blank fields in a form versus opening a query datasheet?

[@599CD]

I cover this in my full Access Search Seminar. You can essentially use the query to feed data into a form, and then open that form. You can also use a SUBFORM on the search form itself, but that involves a little code (or a refresh macro) to requery it. See 599CD.com/XHL4AA for details.

[@johnathanaitken5555]

599CD Computer Training Thanks. I'll try what you said and see what happens.

[@mahdimariani6149]

I am trying to make this form for a database at work, but every time i run the query I get zero results. I also have Access 2010 and have been following all the steps in the video. What could be causing the problem?

[@599CD]

Without seeing your query it's impossible to tell.

[@boopiechot]

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

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

[@BradKnox72]

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.

[@technogandhi]

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

[@599CD]

First, if you're a novice, watch my complete 2.5 HOUR beginner Access tutorial (right here on KZbin). 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.

[@599CD]

I'd really need to see your database to get a better idea of what you're trying to do. You can use the SUM function in the footer section to get a count of items.

[@jupiswhat]

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.

[@markparsons4434]

Great lesson on forms, but I can seem to be able to have the form close after the query has run?

[@599CD]

I don't understand.

[@jesterface83]

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

[@afurneaux1]

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

[@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.

[@bmbarney1]

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?

[@imodem]

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.

[@s3oods10]

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

[@BWInnTowner]

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?

[@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.

[@599CD]

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

[@sassycyra]

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?

[@maverick040690]

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?

[@shrye2009]

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.

[@599CD]

Sure. Just use that query to feed a form. Display the form instead.

[@ekilibrium34]

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?

[@ailyssify]

is it the same if you replace the textboxes with a drop down list?

[@britesynth]

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!

[@britesynth]

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!

[@chrisscura925]

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.

[@deadcoww]

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!

[@599CD]

It's impossible for me to tell what you're doing wrong without looking at your database. I can tell you that I've used this technique in MANY of my classes and seminars, and it works in EVERY version of Access - as long as you follow the directions exactly. Try building the example that I build. Don't try to apply it to your database until you get that working first.

[@599CD]

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

[@digicomi7133]

Sorry if this has already been asked, but what can I do if some information is missing? (eg: someone didn't enter their last name) at the moment entire records are not being searched for because of a few missing bits. Thanks for this video you are a hero!

[@599CD]

I'm not sure. I'd have to see your database to tell you what you've done incorrectly. You can send it to me via then TechHelp page on my web site if you want and I'll look at it when I have some time.

[@abelmartinezky9423]

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

[@GeminighESO]

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

[@abelmartinezky9423]

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

[@GeminighESO]

Ok thanks!

[@nickhorton7898]

+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] & "*"

[@jochapman4553]

+Abel Martinezky You can use the nz function in the query to return " " if the field is empty nz([field], " ")

[@TimWeaver]

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?

[@maverick040690]

Some of the fields are currently blank for all records, these seem to be the ones giving problems. Should there be a reason for this? How can I get round it?

[@deagantje]

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?

[@LuisMaia1]

Hello Richard, in a "web" database how can you do this "Run a Query" from a Button? I didn't found a work around :-) Thanks! Your Videos are excelent!

[@BWInnTowner]

I had a similar problem. Everything was spelled correctly, but the query with the criteria EXACTLY as specified returned all records. Any suggestions?

[@599CD]

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

[@gartaz]

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

[@cloydvillaflor9939]

Hi!My search form does not function unless I save it before running the query. Please help. Thank you!

[@boopiechot]

I have a text field for gender whose value comes from a form. I have a complex interface for the query form. I have set the default values of all the text fields to null and now when the user selects from a drop down or radio buttons, the invisible text fields are populated with the values. When I run the query for the first time, it works, but when i try to play around wit different conditions,it does not return any value.I think I am facing this because of the default values in the text fields

[@apstones]

it works great, however i cant seem to get it to work for more then 1 check box. for example, i have 5 items that are simple yes or no questions, if i try to drill down, information like you have in text its fine. it wont work with check boxes?

[@abelmartinezky9423]

+Andrew S. Phelps Check boxes you use true/false or 0/1 to check their state

[@599CD]

What he said. :)

[@dlafko]

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?

[@harperassociates]

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?

[@scottb4087]

Is there a way to make a multi-field search like you did here, except you have some of your records have blank data? I want to have a multi-field search form that will still display records that have one or more of the search criteria as a null value, but the record still matches at least one of the search criteria.

[@599CD]

Blank fields start getting into some really complicated techniques using IsNull() and such. I cover many more in-depth scenarios in my full Search Seminar: 599CD.com/X8ZW57

[@Gtrboy1123]

Thanks for this video. All working fine, except it doesn’t include in the search any new records I’ve entered into the database since the query was built. Any advice please?

[@599CD]

599cd.com/qq

[@Numbers112]

Can you point me to the tutorial where I can setup the query results to show as a report or form?

[@599CD]

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

[@TayUndecided]

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!

[@phillavonas3285]

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?

[@599CD]

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

[@yamahajt]

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

[@HabibiComeToCali]

when i select the table for combo search , i do not see all fields. there are fields missing on which i actually wants the filter and they are text datatype fields.?

[@599CD]

Are they Long Text? You can't put Long Text (Memo) fields in a combo box.

[@KevinCarterSound]

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

[@laurat.6471]

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.

[@laurat.6471]

Laura T. I'm having the same problem now. If you find a fix, please let us know.

[@599CD]

See 599cd.com/SearchForm 2.0

[@easkew78]

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.

[@guidomassaro7490]

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.

[@PriyaSharma-kw5ri]

having the same issue ... no idea what is happening

[@ristantosky]

Try to replace exclamation marks with dot

[@599CD]

See 599cd.com/Search2

[@HabibiComeToCali]

second question.. i created a search bar on the form header and its working but the records are editable or can be deleted. How can i restrict users to just search only. i tried the restrictions from sheet properties but it also restrict the search bar and you cannot enter the data on search bar.

[@599CD]

What search bar?

[@minhajdesigns]

I am getting the dialogue box of "ENTER PARAMETER VALUE" while opening or running the query. How can i solve this?

[@lDominicl]

Late to the party here, but for future peeps. Change the code he uses to Like "*" [Forms]![Search Function]![Name] "*"

[@599CD]

599cd.com/EPV

[@shrye2009]

I want to make a report using 4 different queries but I keep getting an error saying can't connect tables. Any Suggestions on how to fix this issue?

[@whoyathought400]

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

[@waqasahmed9065]

HI, HOW CAN WE SEARCH THE DATA OR FILTER THE DATA IN BETWEEN TWO DATES ON THE SAME FORM WITH TEXT BOX.

[@599CD]

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

[@petermoore9358]

This will solve many problems for me, however, I would like to filter at the top of the form and display the results in a continuous form below. Can you help?

[@599CD]

599cd.com/SearchSeminar

[@jonykk]

Hi there, can you give the link after you have update your Access Search Seminar...Thanks a lot...your video are very helpful...

[@leiffrancisco8724]

Hi! how to get no values when you run query. I'm kinda focusing on hiding data unless a specific data is input in the search form

[@599CD]

I don't understand the question. Are you looking for NULL values?

[@599CD]

If your database is split properly, you should see the SAME data on all machines when you open a table, query, or form (that's based on the same table). Without seeing what you have there, it's impossible for me to tell you what's wrong, however.

[@monayassin5619]

Can you please help me if I need the search button to grab the null data too? this one only works when all the data are there what about if one field is null?

[@599CD]

Feel free to submit your question here: 599cd.com/TH

[@eliasaouad13]

I have followed the instructions in the video step by step, but the search results always showing up in Datasheet instead of Form View. Not sure what I am doing wrong.

[@599CD]

Impossible to tell without seeing your database. Start over. Blank new database.

[@sudhirsinghthokchom2634]

Sir, how can an attachment file can make as default or original in the form for all the data to enter.

[@599CD]

Sorry, I don't understand your question.

[@BrownAdventuresUS2]

I did everything stated in the video, however, after putting a value in my form field (account name) and run my query it's blank. Also when all of my form fields are blank and I run my query I get nothing unlike the results in the video. Could use some help.

[@GeminighESO]

+David Brown Did you figure out how to fix this? I also have the same problem with blank results.

[@599CD]

See 599cd.com/Search2

[@shan-ot6lv]

is there a diffrent if i want ti use combo box insted of text box ???????i want to do this searchq by combo box please

[@599CD]

Works the same if it's a value list combo box.

[@EnochBeats]

Hello this video was very informative! It helped me with a project at work. . Question, How would i have the selected record in the query show up in a Main Form that i already have. . The user could search based on their criteria, and then select a record from the query results, and have it open up in my "main form" is this even possible??? Thanks for any input.

[@astherth]

Hi, can I ask. how do you create a search form that is popped up. my search form is worksheet style :(

[@Barnes16ae]

What if you did want access to open another form, you wanted it to show up in the same form when you run the query. Can you add a subform that will show the data in the form vs opening up another form

[@599CD]

That's definitely possible