favor08
Jul 25 2007, 09:50 AM
I am trying to import a text file and for some reason I can not get my date field called "Due" to covert to from a text to date format 7/01/07
freakazeud
Jul 25 2007, 12:54 PM
Hi,
I'm not sure if an import specification can grab that date format. You might be better of importing it as a text field and then using a UDF to run with an update query to construct the actual date values. In the UDF you can use the DateSerial() function and a select case statement to construct what you need e.g.:
Public Function ConvertDates(strDate As String) As Date
Dim strMonth As String
Dim monthInt As Integer
If strDate <> "" Then
strMonth = Mid(strDate,3,3)
Select Case strMonth
Case "JAN"
monthInt = 1
Case "FEB"
monthInt = 2
Case "MAR"
monthInt = 3
Case "APR"
monthInt = 4
Case "MAY"
monthInt = 5
...'all other months
Case Else
monthInt = 0
End Select
ConvertDates = DateSerial(Right(strDate,4),monthInt,Left(strDate,2))
End If
End Function
This is just a quick untested sample but should get you started.
HTH
Good luck