asked on

Text to Date type

I have a text field “EndDate” in my table, half of data have format: mm/dd/yy the rest dd-MMM
I want to convert all data from text to date type format: mm/dd/yy.
Data in this field are like :
01-Jan
02-Jul
20-Dec
24-Dec
12/26/01
12/27/01
I want to put year 2001(01) at the end where it missing and then convert all data to date type.
May data in “EndDate “should look like :
01/01/01
07/02/01
12/20/01
12/24/01
12/26/01
12/27/01

Thanks

Taras

nico5038

Then I would use Cdate(field)

In an update query use:

IIF(len(stringdate)<7,Cdate(stringdate&"-2000"),Cdate(stringdate))

Nic;o)

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Taras

ASKER

Hi Andrew.
This is working fine.
Thanks for your help,
Taras.

Taras

ASKER

I have just one small problem Andrew it shows me year 2002 and I want 2001.
Any idea what I did wrong?
Taras.

TextReport

Change the update query so it does a select with the update to part as another field and past some of the datefield and calculated field as an example of where they are going wrong, also, what version of Access is it?

Cheers, Andrew

nico5038

Looks like my comment has been invisible....

Nic;o)

Taras

ASKER

Hi Nico5038.
I tried you answer and it shows 2001properly and that is what I need.
I will post this question again to allocate points to you.

Thanks Taras.

nico5038

Thanks for the gesture ;-)

Both solution will work, but both have the same "problem" that an invalid date won't be processed correctly.
You need to be sure that no Nulls or other garbage is in this field.
From that point of view you should first run a select query like:
SELECT iif(isdate(myolddatefield), "Yes",IIF(IsDate(myolddatefield & "-2001"),"Yes","No")) AS DateOK From tblWithDate WHERE DateOK = "No";

This will show the problem fields.

Nic;o)