formulas to convert a number to a date 1

[ladyemrys]

Hi everyone,

I have an access database that is set up like a giant spreadsheet. I'm tasked with trying to turn it into a relational database but am having trouble with the field that holds a plan approval date. I've extracted the data from the database to an excel spreadsheet, and it comes over as a number. Here is a sample of the data:

Plan Approv DATE

20110405
20140327
20140414
20130509
20140714
20140703
20140424
20140620
20140805

I have attached crystal to this as a datasource (csv) and have tried to apply the following formula to it:

Date(tonumber(totext({EMPLOYER_STATISTICS_csv.Plan Approv DATE},0,"")[1 to 4]),
tonumber(totext({EMPLOYER_STATISTICS_csv.Plan Approv DATE},0,"")[5 to 6]),
tonumber(totext({EMPLOYER_STATISTICS_csv.Plan Approv DATE},0,"")[7 to 8]) )

and I've also toyed with these possible options:

//Cdate(Totext({EMPLOYER_STATISTICS_csv.Plan Approv DATE},'MM/dd/yyyy'))
//date(mid(totext({EMPLOYER_STATISTICS_csv.Plan Approv DATE},0,''),5,2)+'/'+ right(totext({EMPLOYER_STATISTICS_csv.Plan Approv DATE},0,''),2) + '/'+ left(totext({EMPLOYER_STATISTICS_csv.Plan Approv DATE},0,''),4))
ToText({@PlanApprovalToDate}, "dd-MMM-yyyy")

When the report runs, it shows up as a date fine, but when i try to export the report it gives me the following error (using the first option):

a month number must be between 1 and 12.

Any help would be greatly appreciated.
LE

 

[SkipVought]

Hi,

"A month number must be between 1 and 12"

In your Access query use an immediate if to determine month numbers that exceed 12 and return something appropriate. Chances are your database has some dates like 99999999. This is typical when a date is undefined, such as an end date, for an event that has not yet ended.

 

[ladyemrys]

Hi SkipVought,

Thanks so much!!!

Would null values also do this? That would make sense, they are very inconsistent in entering data altogether...

Thanks again!
LE

 

[SkipVought]

Where YourMonthNbr Between 1 And 12 ......

 

[ladyemrys]

Got it, thanks again. YOU ROCK!!!!

 

[SkipVought]

But again, do you need to do something with the date fields with illegal dates?

Maybe in the Select statement...

IIF(YourMonthNbr>=1 and YourMonthNbr<=12,DateConversion,???)

 

[ladyemrys]

Yeah that's what i'm trying to do now, either ignore the null values or something. This is another great idea, i'll give that a try. Thanks so much!!!