title: SQL statement for combobox row source
body: <p>I'm trying to define a SQL statement to use as the Row Source for a ComboBox on an MSAccess form. The SQL should select records from a table tblI where a particular table field matches a variant parameter varS set by the user; however, if varS is Null or not present in another table tblS, the SQl should select all records in tblI.</p>
  
  <p>I can code the first parts of this (varS matches or is null):</p>
  
  <pre><code>SELECT tblI.ID, tblI.S FROM tblI WHERE ((tblI.S = varS) OR (varS Is Null)) ORDER BY tblI.ID;
  </code></pre>
  
  <p>Where I'm struggling is incorporating the final element (varS not present in tblS). I can code a test for the absence of varS in tblS:</p>
  
  <pre><code>Is Null(DLookup("[tbls.ID]","tblS","[tblS.ID]= " &amp; varS))
  </code></pre>
  
  <p>but I can't work out how to incorporate this in the SQL statement. Should this work?</p>
  
  <pre><code>SELECT tblI.ID, tblI.S FROM tblI WHERE tblI.S = varS OR varS Is Null OR DLookup("[tbls.ID]","tblS","[tblS.ID]= " &amp; varS) Is Null ORDER BY tblI.ID;
  </code></pre>
  
  <p>When run as a query it returns every record in tblS no matter the value of varS.</p>
  
  <p>Table structure:</p>
  
  <p>tblI contains 2 fields, Autonumber ID and Long S</p>
  
  <p>tblS contains 1 field, Autonumber ID</p>