title: Multi-field searches in Access with blank fields
body: <p>I have a table in access (simplified) with fields of sex, first name, last name, and phone number.</p>
  
  <pre><code>Sex     First    Last     Phone
  F       Alice    Smith
  M       Bob               111-111-1111
  F                Smithe    111-111-1112
  M       Charlie  Smith     
  F       Eve      Drop      111-111-1113
  </code></pre>
  
  <p>I have created a form along with a query to search for the records, using the criteria of </p>
  
  <pre><code>Is Null Or Like "*" &amp; [Forms]![Search]![Criteria] &amp; "*"
  </code></pre>
  
  <p>where Search is the name of my form, and Criteria is the name of the individual entries on my form.</p>
  
  <p>Right now, if I search for Sex F and the last name Smith, and leave the first and last name fields blank, I want to be able to see the records for "Alice Smith" and "Smithe". But instead, I only get the record of "Alice Smith". Furthermore, if I search for Sex F and phone number 111, I get results for Alice, Smithe, and Eve. Similarly, if I just search for phone number alone, I will get all five records.</p>
  
  <p>I'm assuming that I'm doing something wrong here, but I can't tell what it is. Based on the conditional logic of or, the results should be either Null or 111 (and similar for the other fields), so I guess it is behaving as expected. I've tried xor however, and I get the same result (and even if it does work the way I'm thinking it would, it would seem bad since a blank entry would be interpreted as null instead of "search every entry". I've tried using iif, which should be the correct way to do this, but it appears to not work when placed in the criteria field. Am I missing something here? </p>