MS Access – Show All Records if Form Control is Left Empty

Have you ever used a form to allow the user the ability to make selections and then applied them as criteria in a query?

Have you ever wonder about how can I allow the user to make a selection to perform a filter, but if it is left blank then display all the records?

The solution is rather simple, and one that I picked up several years back thanks to a discussion or two involving Marshall Barton and John Vinson (2 Access MVPs).

So normally you’d create a query criteria similar to:

=Forms!YourFormName.YourComboBoxName

To enable it to handle a Null entry, and thus display all the records if the control is left unpopulated, you simply change it to:

=Forms!YourFormName.YourComboBoxName Or Forms!YourFormName.YourComboBoxName Is Null

 

Now I’ve seen people using SQL along the line of (I have to admit I did so myself when I first started using Access – we all started somewhere!):

Like "*" & Forms!YourFormName.YourComboBoxName & "*"

and while it will work, LIKE is a much less efficient search mechanism in any database (not just Access), so it is a best practice to use the criteria as demonstrated by John and Marshall for optimal performance.  In small recordsets either technique will work just fine, but when you start dealing with larger recordsets, the difference becomes very noticeable, very quickly since LIKE cannot use indexes to apply the criteria and has to go row by row.

19 responses on “MS Access – Show All Records if Form Control is Left Empty”

  1. Mike

    If you’re ever in Charlotte, send me a message cause I owe you a beer! Thanks, that was MUCH easier than I thought (finally something simple actually was in access!)

  4. Jess F

    Programming in Access almost 20 years and I NEVER knew this was so simple. Literally saved my day. I love learning new stuff and sharing knowledge.

  7. Patrick

    Holy crap I have been struggling with this for years. I never thought to set the criteria equal to the combo box being null. Thank you so much.

  8. zoyzoy

    I don’t even understand how this works, but it does !
    Thanks a bunch for this very simple yet smart way to solve that problem 😀

  10. KSabai

    I tried using =Forms!YourFormName.YourComboBoxName OR Forms!YourFormName.YourComboBoxName IS NULL in a query. had to use them for 3 combo box controls. what did was populate so much in the criteria rows and ultimately i received and Sql error. iam using SQL Server as Backend

  13. Louis

    Thanks Daniel,
    I thought I had the same problem but why does it not work for me?
    Access automatically changes the query criteria from:
    =Forms!YourFormName.YourComboBoxName OR Forms!YourFormName.YourComboBoxName IS NULL
    to:
    Forms!YourFormName.YourComboBoxName
    This also should work with a textbox in stead of combobox?

    1. Daniel Pineault Post author

      Text boxes are a pain when it comes to this! You will most probably need to pull the Text property value.

      Me.TextboxControlName.Text

      rather than the default

      Me.TextboxControlName
      Or
      Me.TextboxControlName.Value

      1. Louis

        Thanks for your help Daniel,

        I found that your initial code (=Forms!YourFormName.YourTextBoxName OR Forms!YourFormName.YourTextBoxName IS NULL) was automatically changed by Access in 2 columns:
        – column with criteria: “Forms!YourFormName.YourTextBoxName”
        – a new column behind all my query columns : “Forms!YourFormName.YourTextBoxName”, expression, Is null (Or-criteria-row)
        i.e. Access split your code into 2 columns but I did not see that last column, due to the mount of columns.

        For selection of the report I am afraid I still have to use “Like”.
        The user fills the textbox field with various descriptions of text.
        So I have to give the user the opportunity to select in a printdialog textbox based on characters using wildcards for example Chev* or *evrol* to select rows of Chevrolets.

        Maybe not as efficient as without “Like” but it provides the right output….

  14. Hat2Boots

    Lol, I did not think this would be SOOOOO easy. I was trying IF statements and such. Copy/paste, ya gotta love it.

    Thanks Daniel

  16. Jeff Kraft

    Thank you thank you thank you. I know this is an old post but I’ve spent alllllll day looking for the answer. The only other person that I knew did wanted to charge me a lot for a simple thing like this.