Making a better search form for Access with SQL and filters.


If you are looking to refine your search experience in Access, we have developed a rather unique way to search multiselect fields from an unbound dropdown on the search form.  It involves creating a large form with fields you call by name, then run their contents into a SQL string which you pass into the form as a filter.  Note that in this example code, I change the Status text field to a Multi Select Simple box.  Then we use the search button to get the contents and turn the values selected into a string which works in the filter:


search in Access Database

This is the relevant code from the Search button, pulling the multi-select values in for my sql string.

Ultimately, I have to still build the string and get the filter applied but this is quite effective in producing a filtered list with more than one option in a single field selected during the search.   You can see my old code above where only one value would have been picked – as a reference point.

Want to know more?  Call us for your custom database design experience.


Related Posts

Access 2016 Relationships

Short blog today, this is a great read on relationships in Access 2016: Access 2016 Relationships Thanks to Microsoft for this one!

Microsoft Access Union Query from Hell. Skill Level: Demi-God

The worst query I ever had to write which powers a master report for the database user.  Here it is.  I don't think I can make it smaller, there were too many weird rules, aggregates, and translations in it.  And we needed to pull different columns based on criteria. ...



Submit a Comment