Convert text value to Short Time

[JH40]

I am going absolutely awol trying to find the formula... I had it in a query, but the db corrupted before I saved the query (Access 2010--no surprise)... I am trying to convert a text field to a time value. I had used something like the following and it worked great! Now I can't recall how to write it.

CTime([date],"@@\@")

I know that is wrong, but it looks somewhat similar. TimeValue, Format and Convert do not work for some reason.

Thank you...

 

[pbaldy]

I don't know that there's any such function. If you're trying to convert text:

?timevalue("8:30")
8:30:00 AM

 

[JH40]

Let me clarify by example above. I indicate "date" in error. There is no "date" data. The raw text value is for example:

900

and needs to show as 9:00 AM

no date is involved.

 

[JH40]

Thanks for your input Paul. I actually found it! Here it is in case you ever need it. Works so well...

CDate(Format([admit time],"@@\@"))

 

[pbaldy]

Glad you found your solution.

 

[nyaworld]

Thanks for your input Paul. I actually found it! Here it is in case you ever need it. Works so well...

CDate(Format([admit time],"@@\@"))

I put this into my query and when I run it, I get "#Error".

Real Time: CDate(Format([Start Time],"@@\@@"))

The value I'm trying to convert is a text string. So, 17:30:00.000 is supposed to be 5:30PM, but I can't convert it. I got excited when I saw this post, but it doesn't work.

I'm using Access 2013 if it matters. :banghead:

 

[pbaldy]

If your value is text, try reversing the functions. You want to convert it to a date/time value, then format it.

 

[arnelgp]

you missed a colon in your format.

 

[nyaworld]

you missed a colon in your format.

You are right, I did. I got all excited and tried it the correct way and STILL got an error message:

Real Time: CDate(Format([Start Time],"@@\@"))

#Error

 

[nyaworld]

If your value is text, try reversing the functions. You want to convert it to a date/time value, then format it.

How do I convert the field (a text value) into a time value? Can't I do this in a query? If I can, how? I have tried all sorts of functions and I still get #Error.

 

[pbaldy]

Like I suggested in your other thread:

?cdate("17:00:00")
5:00:00 PM

 

[arnelgp]

TimeValue(Format([Start Time], "00\:00")) or
CDate(Format([Start Time], "00\:00"))