.txt file linked tables Date error (#Num!)

[sphynx]

Hi everyone

As the title says I have been trying top link .txt files to a new Access table.

Basically I have 3 x delimited log.txt files generated from some manufacturing equipment, it is proposed that that these logs are used to generate down times, efficiencies etc

I have successfully manipulated these files into excel. but decided i wanted these in access so I can tie them into another manufacturing DB that is running.

I have managed to link the Tables to the .txt file through the wizard. The problem is that I have 2 different sorts of date & time formats within one of the .txt files that is giving me #Num! error, these are:

1) Date & Time in Seperate Fields (Time & Date Log Entry Made)

Date: Y - M - D
Time HH:MM:SS

2) Date & Time in the same field (Time & Date of Equipment status Change)

Date/time: Y - M - D HH:MM:SS


I am unable to change the way in which the log file is created & unfortunatley require all the fields, does anyone have any ideas?

Any help would be greatley appreciated

 

[neileg]

Which one errors out?

 

[sphynx]

Sorry the second

Date & Time in the same field (Time & Date of Equipment status Change)

Date/time: Y - M - D HH:MM:SS

Is the format that is causing the error

 

[neileg]

I think you will need to treat this field as text and use a query to convert it to a date/time value.

 

[sphynx]

I came to the same conclusion this morning before I read your post, I can confirm it works this way

Thanks for the help