Microsoft Access Multi-Field Search Form

In this tutorial you will 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 http://www.599cd.com/tips/access/multi-field-search-form-like/?key=YouTube

Category:

Education

Tags:

• microsoft
• access
• access 2010
• search form
• search
• multi-field
• like
• wildcard
• query
• forms

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

[bandbay]

thank u too much

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

[msmikamika]

@maryspin249 hello! i just tried this on access 2007 and it worked! the only thing is you have to remember to save and then run the query.

[ted60211]

Or the use of Refresh Requery methods would do it.

[ted60211]

I would suggest on enterprise level database with many records that you don't use wildcards in front of criteria values because it will not allow you to index values correctly. Also, I like to label a customer button for example:tbCustomer. If you ever have to make a lot of changes it helps with intelisense when in the VBA editor. It is a good video. I would suggest using something like docmd.findrecord to take you to a record on a form with it.

[ReneeM787]

How do you change this when the field you want to allow users to populate (or not) is a date you want to use in a range?

[PLAYVEDIO1]

is very important!

[maryspin249]

This does not seem to work in Access 2007. The search returns nothing :( Any ideas on differences that apply to Access 2007?

[maverick040690]

Sorry to keep posting! I've worked out that the problem is to do with Null values in the fields and have written "Is Null" in the "or:" entry under "Criteria:" for each of the fields. I thought this would solve it, however it has not. Does anyone have any advice on the matter?