title: Access Low and High fields and keeping fields blank in a form
body: <p>First off I had the problem of if I wanted to keep a field blank on my form and only search on another field (Such as if you had 2 fields but I only wanted to search the database for one, and keeping the other blank) it would come back with the right results.</p>
  
  <p>I did this by using: Like [Forms]![BackUpValidationForm1_3]![AccountCMBO] &amp; "*"
  in all of the fields on my form and just changing the textbox area to the new textbox.  This worked like a charm.</p>
  
  <p>But now My new question is I need to keep that functionality and also allow for a range.  </p>
  
  <p>I have an access link that is to a server table. The table has a column that has a low number and then a separate column that has a high number. For example, the low could be 100 and the high could be 200. This means that any account that falls in between those 2 numbers pass the test of this table. So If I had an account that had an account number of 101 and I put that in my form and click the run button it should bring back any row that has both fields as 101 (so low and high is both 101, meaning this is the only account that this rule applies to) and any other row that has say low of 100 and high of 200 because its in between both of those. I have a few columns like this, account low and high, function low and high.</p>
  
  <p>Basically with just using the criteria that I started with (Like [Forms]![BackUpValidationForm1_3]![AccountCMBO] &amp; "*") it would not capture those records that have a range that may be under it and then above it meaning its in the middle some where.</p>
  
  <p>I have tried: Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH] and it works by itself.  But if I put that same thing in the other field that is on this form too and I leave that field blank and I just search for account in the form it will bring back no results.</p>
  
  <p>I've tried the following:</p>
  
  <p>Like "<em>" And Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH] &amp; "</em>" and also tried Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH] &amp; "*" and for the other field the same things in conjunction with each other and neither will produce data together</p>
  
  <p>Also Tried: 
   Like [Forms]![ValidationForm1_3]![AccountCMBO] &amp; "<em>" And Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH]
   with the same thing in the other form field:
   Like [Forms]![ValidationForm1_3]![FunctionR3CMBO] &amp; "</em>" And Between [OP_TYP] And [OP_TYP_HIGH]</p>
  
  <p>And also tried putting an "Or" in between them instead of an "And"</p>
  
  <p>Anyone know how I can fix this?</p>
  
  <p>My Actual Access SQL:    </p>
  
  <pre><code>SELECT VALIDATION_RULE3.ACCOUNT_CD, VALIDATION_RULE3.ACCOUNT_CD_HIGH,
  Rule3_Accounts.Description, Rule3_Accounts.D0_DESCA, Rule3_Accounts.D0_TYPE, 
  Rule3_Accounts.D0_PARENT, Rule3_Accounts.D0_WEIGHT, Rule3_Accounts.D0_LEVEL,
  Rule3_Accounts.D0_CHILD, Rule3_Accounts.D0_PRIORITY, Rule3_Accounts.D0_STATUS,
  Rule3_Accounts.D0_CHILDSORT, Rule3_Accounts.D0_CREATE, Rule3_Accounts.D0_MODIFY, 
  Rule3_Accounts.D0_BALTYPE, Rule3_Accounts.AccountText, VALIDATION_RULE3.XPN_OP_TYP_CD
  
  FROM Rule3_Accounts INNER JOIN O8_VALIDATION_RULE3 ON Rule3_Accounts.AccountText = O8_VALIDATION_RULE3.ACCOUNT_CD
  
  WHERE ((([Forms]![ValidationForm1_3]![AccountCMBO]) Like [Forms]![ValidationForm1_3]![AccountCMBO] &amp; "*" Or ([Forms]![ValidationForm1_3]![AccountCMBO]) Between [ACCOUNT_CD] And [ACCOUNT_CD_HIGH] &amp; "*")
  AND (([Forms]![ValidationForm1_3]![FunctionR3CMBO]) Like 
  [Forms]![ValidationForm1_3]![FunctionR3CMBO] &amp; "*" 
  Or ([Forms]![ValidationForm1_3]![FunctionR3CMBO]) Between [XPN_OP_TYP_CD] And [XPN_OP_TYP_CD_HIGH] &amp; "*"));
  </code></pre>