number problem

[doylie]

I have a problem where by I need to change values in an access table which actually represents a date, they are stored like yymmdd, I have preceeding zeros to represent 2000 etc...ie. 000101 = jan 1st 2000. The problem I have is when I try to convert these dates into a new field name using:-

CDate(Left(myFld,2) & "/" & Mid(myFld,3,2) & "/" & Right(myFld,2))

I am getting a problem with the conversion some dates it does fine eg. 970125 converts to 25/01/1997 whereas others don't..eg. 000818 converts to 18/08/1981 whereas it should be 18/08/2000. Is there anyway of stopping this or would it be an idea to change the original number to ddmmyy format then do the same conversion. Any help would be much appreciated .
Ps. If I do need to switch the date order any help with how to do it would also be handy.

 

[Drevlin]

I'm a tad confused.
Shouldn't your date conversion read like this:

CDate(Mid(myFld,3,2) & "/" & Right(myFld,2) & "/" & Left(myFld,2))

The way you show it would read 980213 as 98/02/13.

 

[Tim K.]

Just use DateSerial(). Try the code below.

Function Conver2Date2(strDate As String)
Dim NewDate As Date
NewDate = DateSerial(Left(strDate, 2), Mid(strDate, 3, 2), Right(strDate, 2))
Conver2Date2 = NewDate
End Function