title: Howto Validate External Data Source before Importing in MS Access
body: <p>I have an MS Access Database that uses an external text file for input.</p>
  
  <p>I don't trust this text file and I want to validate the contents before I import the data to avoid contaminating my database.</p>
  
  <p>The data is imported from the text file via a Macro which is working perfectly fine on its own. I am trying to wrap the query that does the insert inside an "If" Program Flow block in the macro.  I am having trouble getting the condition in the <code>If</code> statement to work.  </p>
  
  <p>The external text file source is known as the table <code>UserStatistics</code></p>
  
  <p>I have created a query (<code>UserStatistics-CheckTxtFileIsCorrect</code>) that contains the following code:<br></p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT *
  FROM UserStatistics
  WHERE (((UserStatistics.[Column1])="KNOWNGOODVALUE"));
  </code></pre>
  
  <p>The <code>If</code> condition in the Macro is currently set to: <br></p>
  
  <ul>
  <li><code>count (*) from [UserStatistics-CheckTxtFileIsCorrect] &gt;1</code> but this errors out.</li>
  </ul>
  
  <p>Everything I've tried fails with the error "cannot parse..." or "cannot find the name you entered..." </p>
  
  <p>Any help would be greatly appreciated!!</p>
  
  <h2>UPDATED with list of variations I've tried:</h2>
  
  <pre class="lang-sql prettyprint-override"><code>Count([UserStatistics-CheckTxtFileIsCorrect])&gt;1
  -- "Access cannot find the name 'UserStatistics' 
  -- you entered in the expression" 
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>Count[UserStatistics-CheckTxtFileIsCorrect] &gt; 1 -- cannot parse
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>count (*) [UserStatistics] &gt;1 -- "cannot parse..." 
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>Count * 
  where [UserStatistics-CheckTxtFileIsCorrect]![User ID] = 'ABC' -- cannot parse
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>Count(select * from [UserStatistics] 
  where [UserStatistics]![Column1] = 'ABC') &gt; 1 -- cannot parse
  </code></pre>
  
  <h2>MAJOR UPDATE 2</h2>
  
  <p>HansUp suggested <code>DCount</code>.  If I omit the criterion part of the expression the <code>If</code> condition is now evaluating.  But the criterion part is definitely needed to acheive my goal.
  </p>
  
  <pre><code>DCount("*","UserStatistics","[UserStatistics]![Column1] = 'ABC' ")&gt;1
  DCount("*","UserStatistics","Column1 = 'ABC' ")&gt;1
  DCount("*","UserStatistics",Column1 = 'ABC' )&gt;1
  </code></pre>
  
  <p>All of the above give an error 2001</p>
  
  <h2>Solution!</h2>
  
  <p>It turns out that my external text file column names contained spaces.  So the column in the criterion of the DCount statement needed to be wrapped in square brackets like so:</p>
  
  <p>If <code>DCount("*","UserStatistics","[User ID]='KNOWNGOODVALUE'")&gt;1</code> Then <br>
  <code>Do my Actions here....</code><br>
  Else<br>
  <code>MsgBox Error here...</code><br>
  End If</p>
  
  <p>Many Thanks to HansUp for pointing me towards <code>DCount</code>. </p>