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

StephenB

Registered User.
Local time
Today, 11:56
Joined
Apr 18, 2002
Messages
101
[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.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:56
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 11:56
Joined
Apr 18, 2002
Messages
101
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

Wino Moderator
Staff member
Local time
Today, 08:56
Joined
Aug 30, 2003
Messages
36,118
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.
 

Users who are viewing this thread

Top Bottom