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

[StephenB]

[Solved] 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.

 

[pbaldy]

You've made it text with your default. Try

IIf([Original Coverage Term Date]="00000000",#12/31/9999#,...

Though it may need to be a more realistic date.

 

[StephenB]

You've made it text with your default. Try

IIf([Original Coverage Term Date]="00000000",#12/31/9999#,...

Though it may need to be a more realistic date.

Bingo, Thank you.
I do wonder though, regading "Though it may need to be a more realistic date"...
I've worked for the same company for almost 20 years, and it's the only corporation I"ve worked for. They've always defaulted "active" records (term date - "00000000") to 12/31/9999. Is there a better, or industry-wide approach to this? Perhaps leaving the term date null?

 

[pbaldy]

Happy to help. I'm not aware of a "common" default, I just didn't know if Access would barf and throw an error. If it works, go for it. It would certainly be better to remain consistent within the company.