Importing date values from a text file 1

[Junkie88]

hello,

I have written the following code to import values from a text file and append them to a table. The problem I am having is that the date value form the text file gets changed after being imported to the table. The date field in the table is formatted to accept medium dates. Can someone please let me know what is wrong?

Dim ReadedData, Db, RST, Counter

Set Db = CurrentDb
Set RST = Db.OpenRecordset("SELECT * FROM PDALOGS")
With RST

Open "C:\Documents and Settings\Entanglement\Desktop\pdalog.txt" For Input As #1
.AddNew
.Fields(0) = Now
While Not EOF(1)
.AddNew
For Counter = 1 To 7
Input #1, ReadedData
.Fields(Counter) = ReadedData
Next
.Update
Wend
Close
.Close
End With


Here is what a row in the text file looks like:
1 1 7/12/07 0 1 0 0

 

[KenReay]

Hi

I assume .Fields(3) is a date/time type?

If yes try

If Counter = 3 Then
.Fields(Counter) = CDate(ReadedData)
Else
.Fields(Counter) = ReadedData
end if

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JerryKlmns]

In this line
1 1 7/12/07 0 1 0 0

7/12/07 seems to be of a date format of d/m/yy. So the date string starts at position 5 and has a length of 6-8 characters. You 'll have to find the last occurence of the / and add to its ordinal position the value of 2 [to include the year part] thus to find the last position of the date string character. Subtracting that value, 4 you 'll get the correct date.It would be much easier the have a date format of fixed lenght like dd/mm/yyyy or yyyy-mm-dd or whatever

I was wondering though, how that line was imported as date in the field!