Is there a better way to deal with this date?

[Fred Wilson]

Hello,
I am using Access 2002.
I receive and excel file that has two date fields with the following format
m/d/yyyy
For what ever reason when importing, they import as text. When I switch
them to dates Access interprets it as d/m/yyyy unless the DAY is greater
than 12.

Here is a code I wrote to fix my problem. I use it in an Update Query.
Is there a better way to do this? Like a query function, stored
procedure? I thought SQL had a function "AsDate"?

Function convDate(strDate As String) As Date

On Error GoTo exit_function

Dim intDay As Integer
Dim intMon As Integer
Dim intYear As Integer

intYear = CInt(Trim(Right$(strDate, 4)))

Select Case Len(strDate)
Case 8
intMon = CInt(Trim(Left$(strDate, 1)))
intDay = CInt(Trim(Mid$(strDate, 3, 1)))
Case 9
If Mid$(strDate, 2, 1) = "/" Then
intMon = CInt(Trim(Left$(strDate, 1)))
intDay = CInt(Trim(Mid$(strDate, 3, 2)))
Else
intMon = CInt(Trim(Left$(strDate, 2)))
intDay = CInt(Trim(Mid$(strDate, 4, 1)))
End If
Case 10
intMon = CInt(Trim(Left$(strDate, 2)))
intDay = CInt(Trim(Mid$(strDate, 4, 2)))
End Select

convDate = DateSerial(intYear, intMon, intDay)

exit_function:
Exit Function

End Function

 

[Douglas J. Steele]

How are you importing? The Import Wizard has an option to let you specify
how the dates are being passed.

 

[Fred Wilson]

How are you importing? The Import Wizard has an option to let you specify
how the dates are being passed.

The import wizard had all those settings grayed out. Additionally, there
was NOT an option to select advanced options.