title: How to validate / scrub text date before conversion using CDATE in Access Query
body: <p>I am using an Access Query to reformat and join multiple files to create an output file.
  I am working with an input file that provides dates in the YYYYMMDD text format. I am trying to use CDATE &amp; mid/left/right to convert to a Date format. However, I have discovered that some dates in the file are invalid, such as 2/30/2019 (20190230), which is causing data mismatch errors.
  I do not have control over the system that provides this file so I need to be able to handle this issue in my database.</p>
  
  <p>I have tried <code>IIF(ISERROR(CDATE()),NULL,CDATE())</code> to no avail. Current Expression:</p>
  
  <pre><code>IIf(IsError(CDate(Mid([firstofSTART_DATE],5,2) &amp; "/" &amp; 
  Right([firstofSTART_DATE],2) &amp; "/" &amp; 
  Left([firstofSTART_DATE],4))),Null,CDate(Mid([firstofSTART_DATE],5,2) &amp; "/" &amp; 
  Right([firstofSTART_DATE],2) &amp; "/" &amp; Left([firstofSTART_DATE],4)))
  </code></pre>
  
  <p>I would be ok with correcting the bad date to anything that can be easily filtered out (such as NULL or "").</p>