Converting Text Dates using CDate() and Querying on the Converted Date

Hello.

I receive a text file with [Original Coverage Effective Date] and [Original Coverage Term Date].

The dates are in YYYYMMDD format.



I need to run a query that pulls records that are active ([Original Coverage Term Date] = “00000000”) or termed within the last year ([Original Coverage Term Date] = “20140219” or later, since today is 2/18/2015.)



I’m using the following on a query to convert the date:

TermDate: IIf([Original Coverage Term Date]="00000000","12/31/9999",CDate((Mid([Original Coverage Term Date],5,2) & "/" & Right([Original Coverage Term Date],2) & "/" & Left([Original Coverage Term Date],4))))



And using the following criteria to isolate the appropriate Term Dates:

#12/31/9999# Or >=Date()-365



Works to some degree because it’s…

Including Term Dates 12/31/9999, 2/21/2014, and 9/30/2014 while

Excluding 1/1/2013, 2/1/2013, and 11/18/2014



However, it’s also…

Including 4/1/2012, 6/30/2013, and 9/9/2012 while

Excluding 10/24/2014, and 11/14/2014.



(I’ve confirmed the above by flipping the criteria to <=Date()-365 And <>#12/31/9999#.



Here’s a sample of the actual data….

Original Coverage Effective date, Original Coverage Effective date

20110301, 20121130

20130901, 00000000

20111101, 20140228

20140901, 00000000

20120209, 20140831

(Commas added for ease of delimitation)



I’m thinking that the “Term Date” date field on the query is still a text field, which is why dates before 02/19 (regardless of year) are not being pulled, while dates later than 02/19 (regardless of year) are being pulled.

Any thoughts on what I’m doing wrong and how I can correct?

Thanks in advance for any insight.

Converting Text Dates using CDate() and Querying on the Converted Date