hopeless DATATYPE

[Lauren36]

I have been asked to repair a database with a few problems.
All of the dates in the database (of about 4000 records)have been entered in a table where the dates are defined as datatype "text" and are in this format "4Jan02". Now they want me to change all of these dates into a format like "01/04/2002" without having to actually go in and retype the dates. I tried to just change the data type from text to date/time but this deleted the dates. I tried changing the input mask and I can't get this to work either. Is there anyway to fix this mess or is it hopeless?

 

[B827]

Before starting make a copy of your data.

If you are sure that the date format is as described for ALL dates then this might help:

Function ConToDate(Tdate As String) As Date
Select Case Len(Tdate)
Case 6
ConToDate = Left(Tdate, 1) & "/" & Mid(Tdate, 2, 3) & "/" & Right(Tdate, 2)
Case 7
ConToDate = Left(Tdate, 2) & "/" & Mid(Tdate, 3, 3) & "/" & Right(Tdate, 2)
Case Else
ConToDate = #1/1/03#
End Select




End Function

The Case Else is there to catch dates not in the stated format. You might want to include some error handling

 

[lhuegele]

Lauren,
Try exporting the table to Excel if it's not too big. We have used Excel to reformat date fields in the past. After you've fixed the date field in Excel, simply re-import it.

Hope this helps,
Larry

 

[Hmadyson]

Function NewDateForm(strDate As String) As Date

Select Case Len(strDate)
Case 6
NewDateForm = DateSerial(CInt(Right(strDate, 2)), ConvertMonth(Mid(strDate, 2, 3)), Left(strDate, 1))
Case 7
NewDateForm = DateSerial(CInt(Right(strDate, 2)), ConvertMonth(Mid(strDate, 3, 3)), Left(strDate, 2))
Case Else
NewDateForm = #1/1/03#
End Select

End Function


Function ConvertMonth(strMonth As String) As Integer
Dim datTemp As Date

datTemp = DateValue(strMonth & " 1, 2000&quot
ConvertMonth = Month(datTemp)

End Function