title: MS Access multi field search with empty fields
body: <p>I have a problem <em>very</em> similar to <strong><a href="https://stackoverflow.com/questions/12820453/ms-access-query-check-if-form-field-is-empty">this one</a></strong>, but I just can't seem to solve it!</p>
  
  <p>In MS Access (2003), I want to search a table based on entries in a number of fields, some of which may be empty.</p>
  
  <p>I have:</p>
  
  <ul>
  <li>text fields</li>
  <li>date fields</li>
  <li>integer fields, and</li>
  <li>a memo field (but we can probably not bother searching this one if it is difficult).</li>
  </ul>
  
  <p>They map onto a table exactly.</p>
  
  <p>I am trying to create a query that will return matching rows when data is entered into one or more of these fields, but some fields can be left blank.  How the heck do I do this?</p>
  
  <p>A query like the one on the linked question works for text fields, but what do I do about the number fields, date fields (and possibly even the memo field)?</p>
  
  <p>To give a clear example, the following code block works for TextField1, but not NumberField1:</p>
  
  <pre><code>PARAMETERS [Forms]![SearchForm]![FilterTextField1] Text ( 255 ), [Forms]![SearchForm]![FilterNumberField1] Text ( 255 );
  SELECT Table1.[TextField1], Table1.[NumberField1], Table1.[TextField2], Table1.[TextField3], Table1.[DateField1], Table1.[DateField2], Table1.[DateField3]
  FROM Table1
  WHERE (Len([Forms]![SearchForm]![FilterTextField1] &amp; '')=0 OR Table1.[TextField1] Like '*' &amp; [Forms]![SearchForm]![FilterTextField1] &amp; '*') AND (Len([Forms]![SearchForm]![FilterNumberField1] &amp; '')=0 OR Table1.[NumberField1] Like '*' &amp; [Forms]![SearchForm]![FilterNumberField1] &amp; '*');
  </code></pre>
  
  <p>I do hope you can help.  I'm sure I'm missing something really obvious, but for some reason my brain feels like it is leaking out of my ears at the moment.</p>
  
  <p>Thank you!</p>
  
  <hr>
  
  <p>If you need it, this is the basic design of the relevant entities:</p>
  
  <h2>Table1</h2>
  
  <ul>
  <li>SomePrimaryKeyWeDontCareAboutRightNow</li>
  <li>TextField1</li>
  <li>TextField2</li>
  <li>TextField3</li>
  <li>NumberField1</li>
  <li>DateField1</li>
  <li>DateField2</li>
  <li>DateField3</li>
  <li>MemoField1</li>
  </ul>
  
  <h2>SearchForm</h2>
  
  <ul>
  <li>FilterTextField1</li>
  <li>FilterTextField2</li>
  <li>FilterTextField3</li>
  <li>FilterNumberField1</li>
  <li>FilterDateField1</li>
  <li>FilterDateField2</li>
  <li>FilterDateField3</li>
  <li>FilterMemoField1</li>
  </ul>