Date Import from Text File

[Davo]

Having problems with type-mismatch when trying to import to a table
(DD_Report). The date field is [DateofReport] with a shortdate format, input
mask of 99/99/00;;_. In the beginning the data wouldn't even import. Code
is shown below -- I've removed some areas that don't have anything to do with
this date problem. Thanks.

Dim req_error As Integer, filedata As String, key As String, flag As Integer
Dim filename As String, temp_date As String, RptDate As Date

ReDim mydata(2 To 8)

Dim MyTable As DAO.Recordset
Set MyTable = CurrentDb.OpenRecordset("DD_Report")
Open filename For Input As #1
flag = 0
Do While Not EOF(1)
Line Input #1, filedata
If Len(filedata) > 0 Then
key = Left$(filedata, 4)
If key = "DATE" Then
temp_date = Trim$(Mid$(filedata, 12, 8))
RptDate = CDate(Left(temp_date, 8))
End If
flag = 1
End If
'Start Import routine to Access Table
If flag = 1 Then
MyTable.AddNew
MyTable("DateofReport") = RptDate
MyTable.Update
flag = 0
End If
End If
Loop

 

[Davo]

Forgot to add, the text file has the data as dd/mm/yy format. Example of the
text straight out of the text file is "15/05/06". I thought this would
import perfectly into the table, but doesn't. Is it due to the slash marks?
Should it be dashes?

 

[Davo]

Disregard, I figured it out for now. Seems Access likes the data to be in
MM/DD/YY order as default so I used some code to reorder the data and it
works. TD is my temp_Date variable. Changes it from dd/mm/yy order to
mm/dd/yy.

If key = "DATE" Then
TD = Trim$(Mid$(filedata, 17, 8))
RptDate = Mid$(TD, 4, 2) & "/" & Left$(TD, 2) & "/" & Right$(TD, 2)
End If

 

[Douglas J. Steele]

If your Regional Settings have been set so that the Short Date format has
been set to dd/mm/yyyy, then what you had should have worked: the CDate
function is one of the few date-related things in Access that respects
Regional Settings.

You do seem to have changed the parameters in your Mid function between the
first and second iteration though.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Disregard, I figured it out for now. Seems Access likes the data to be in
MM/DD/YY order as default so I used some code to reorder the data and it
works. TD is my temp_Date variable. Changes it from dd/mm/yy order to
mm/dd/yy.

If key = "DATE" Then
TD = Trim$(Mid$(filedata, 17, 8))
RptDate = Mid$(TD, 4, 2) & "/" & Left$(TD, 2) & "/" & Right$(TD, 2)
End If

--
Dave
VB Beginner

Having problems with type-mismatch when trying to import to a table
(DD_Report). The date field is [DateofReport] with a shortdate format,
input
mask of 99/99/00;;_. In the beginning the data wouldn't even import.
Code
is shown below -- I've removed some areas that don't have anything to do
with
this date problem. Thanks.

Dim req_error As Integer, filedata As String, key As String, flag As
Integer
Dim filename As String, temp_date As String, RptDate As Date

ReDim mydata(2 To 8)

Dim MyTable As DAO.Recordset
Set MyTable = CurrentDb.OpenRecordset("DD_Report")
Open filename For Input As #1
flag = 0
Do While Not EOF(1)
Line Input #1, filedata
If Len(filedata) > 0 Then
key = Left$(filedata, 4)
If key = "DATE" Then
temp_date = Trim$(Mid$(filedata, 12, 8))
RptDate = CDate(Left(temp_date, 8))
End If
flag = 1
End If
'Start Import routine to Access Table
If flag = 1 Then
MyTable.AddNew
MyTable("DateofReport") = RptDate
MyTable.Update
flag = 0
End If
End If
Loop