Importing Excel File - Time Conversion Needed

[krizma5683]

I am importing multiple Excel files into a table in an Access database, all my columns define themselves properly except for one. I have a column called "Breaks" that should define itself as a Date/Time field, but since there is not any values in the first row, Access defines it as Text when it imports the data.
This causes problems becuase I need to perform some basic calculations against the "Breaks" field, but can't since it's stored as a Text Value. HELP PLEASE!!

My code:

Sub Bring_In_Rte_Deatils()



Set fd = Application.FileDialog(msoFileDialogFilePicker)
Application.FileDialog(msoFileDialogFilePicker).InitialFileName = ("V:\Customer Care\Rtedtail - IPL\*.xls")
With fd
    If .Show = -1 Then
        For Each vrtselecteditem In .SelectedItems
        FileName = vrtselecteditem
    Next vrtselecteditem
    Else
    End If
End With

DoCmd.TransferSpreadsheet acImport, , "IPL Route Time Details", FileName, True

MsgBox FileName & " imported to 'IPL Route Time Details'", vbOKOnly, "Successful Import"
End Sub

 

[krizma5683]

I have figured my own problem out. It was as simple as defining all my columns in the Access table prior to bringing in my Excel data, it then put each column in per my access definition.

 

[Hakala]

Hey, krizma, do you happen to have the code with the change? I'm trying to solve a similar issue. Thanks!