How to validate / scrub text date before conversion using CDATE in Access Query

Question

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 & 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.

I have tried IIF(ISERROR(CDATE()),NULL,CDATE()) to no avail. Current Expression:

IIf(IsError(CDate(Mid([firstofSTART_DATE],5,2) & "/" & 
Right([firstofSTART_DATE],2) & "/" & 
Left([firstofSTART_DATE],4))),Null,CDate(Mid([firstofSTART_DATE],5,2) & "/" & 
Right([firstofSTART_DATE],2) & "/" & Left([firstofSTART_DATE],4)))

I would be ok with correcting the bad date to anything that can be easily filtered out (such as NULL or "").

Gustav Gustav · Accepted Answer · 2019-07-29T10:46:05

You can use DateSerial:

DateSerial(Mid([firstofSTART_DATE],1,4), Mid([firstofSTART_DATE], 5,2), Mid([firstofSTART_DATE],7,2))

and/or filter on:

IsDate(Format([firstofSTART_DATE], "@@@@\/@@\/@@"))

How to validate / scrub text date before conversion using CDATE in Access Query

1 Answers