Import String as date (1 Viewer)

[xgd]

I have the following code which adds part of the imported filename (filetoimport010420071530.txt) as (01042007) to a field

Public Function all()

Dim InputDir, ImportFile As String, tblName As String, FinalName As String
Dim InputMsg As String

InputDir = "C:\Imports\"
ImportFile = Dir(InputDir & "\*.txt")
FinalName = (Mid(ImportFile, 13, 8))

DoCmd.SetWarnings False

Do While Len(ImportFile) > 0
DoCmd.TransferText acImportDelimited, "Import_Spec", "Import", InputDir & ImportFile, True
DoCmd.RunSQL "UPDATE Import SET File_Date = '" & FinalName & "'" & " where File_Date is null"
ImportFile = Dir

Loop

End Function

If the field is set to text it imports fine, but is not a date.
If the field is set to date it does not import anything.
If I dim FinalName as Date it imports as 11/30/4752

This is all very new to me and I am lost!
How can I import the field as a date?

Thanks in advance,

--gd

 

[MarkK]

Use the "#" as a delimiter for date types.

CurrentDB.Execute _
  "UPDATE Import " & _
  "SET File_Date = #" & FinalName & "# " & _
  "WHERE File_Date IS Null"

 

[xgd]

Using # as a delimiter:

I get runtime 3075, If FinalName is dimmed as String
and it imports as 11/30/4752, if FinalName is dimmed as Date

 

[xgd]

Okay, I do not know if this is the most elegant way of doing it, but here is how I ended up doing it.

Set the field in Access as Date/Time
dim FinalName as String
then write the field like this:

DoCmd.RunSQL "UPDATE Import SET File_Date = Mid(" & "'" & ImportFile & "', 13,2) &'/'& Mid(" & "'" & ImportFile & "', 15,2) &'/'& Mid(" & "'" & ImportFile & "', 17,4) &' 12:00:00 PM' where File_Date is null"