Convert text to date

[scubadiver]

Hello,

I am trying to link to a text file. I was having trouble converting 20011975
into a date/time format but it wasn't working. So I formatted the field as a
text file. I have tried converting this to 20/01/1975 in a query but it comes
out with errors and wierd dates.

I've had a look at the other messages and had a go but no go.

thanks

 

[Klatuu]

Regardless of how your dates are displayed either by formatting or based on
regional settings, all date functions exppect a month day year format. To
convert your string to a date field:

strTextDate = "20011975"
SomeDate = DateSerial(Clng(Right(strTextDAte,4)),
Clng(Mid(strTextDate,3,2)), Clng(Left(strTextDAte,2)))

 

[scubadiver]

Brilliant!

Regardless of how your dates are displayed either by formatting or based on
regional settings, all date functions exppect a month day year format. To
convert your string to a date field:

strTextDate = "20011975"
SomeDate = DateSerial(Clng(Right(strTextDAte,4)),
Clng(Mid(strTextDate,3,2)), Clng(Left(strTextDAte,2)))

 

[scubadiver]

I discovered the "advanced" option and it converted the text to dates.

 

[TheNovice]

Can you share? I have the same issue.