Convert large string into DATE/Time Field in MS ACCESS Query

[yomero]

Hi,
I have tried many solutions in this forum, however I am still stuck.
I am linking a large CSV file into MS ACCESS. All fields are TEXT. I tried linking the Date field as Date/Time but was not able to accomplish it.

The example is: 2017-03-19 04:55:38.539038

Using Builder expressions I am able to separate into 2 fields using
- DATE_Only =DATEVALUE(LEFT(DATE,10) )= 2017-03-19
- TIME = TIMEVALUE(RIGHT(LEFT(DATE,19),9))= 04:55:38

[FONT=arial, sans-serif]I join them using the FORMATDATETIME function however the resulting field is not a DATE/TIME field.[/FONT]
2017-03-19 04:55:38

[FONT=arial, sans-serif]Thanks, for your help.[/FONT]

 

[welshgasman]

Try

Sub testTextDate()
Dim strDateTime As String
Dim dtDate As Date, dtTime As Date
Dim dtBoth As Date
strDateTime = "2017-03-19 04:55:38"
dtDate = DateValue(Left(strDateTime, 10))
dtTime = TimeValue(Right(Left(strDateTime, 19), 8))
dtBoth = dtDate + dtTime
End Sub

 

[xenou]

Possibly import rather than link. Then you can work with the data in MSAcess directly as a field in a database table.