Convert Text to Time

I have a Linked table that has access data type Text for time(144754).
Trying to convert This Text field to time with the following but it doesn’t work.  
Any suggestions greatly would be appreciated.

INSERT INTO TestTime ( field60 )
SELECT TimeValue([Field60]) AS Expr1
FROM Test;
ca1358Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what time value do you expect to get from "144754" ?

you can copy the value to a cell in excel, then format the excel cell to time, and it will give you

4/26/2296  12:00:00 AM
0
ca1358Author Commented:
12:00 AM
0
Jeffrey CoachmanMIS LiasonCommented:
Try:

First run a query like this to see if the time ends up being what you want:
SELECT CDate(Left([field60],1) & ":" & Mid([field60],3,2) & ":" & Right([field60],2)) AS NewTime
FROM Test;


If it works, then do something like this:

INSERT INTO TestTime ( field60 )
SELECT CDate(Left([field60],1) & ":" & Mid([field60],3,2) & ":" & Right([field60],2)) AS NewTime
FROM Test;

There may be a better way, but this seems to work OK for me...

JeffCoachman
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Rey Obrero (Capricorn1)Commented:
try this


INSERT INTO TestTime ( field60 )
SELECT TimeValue(format([Field60],"mm/dd/yyyy hh:nn:ss")) AS Expr1
FROM Test;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
> what time value do you expect to get from "144754" ?

> 12:00 AM

That doesn't make sense in any way.
Is this the only example value?

/gustav
0
ca1358Author Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.