Converting Text to Time - hh:nn

[jgabel]

I have been struggling with converting my "start_time" field that is stored as text to a time format of "hh:nn" in my query.

In my query I put the following expression to try and convert from text to time value, but when I do the query reflects 00:00 for all records.

Expression: start_time2: Format(CDate([start_time]*1),"hh:nn")

I attempted to change the field to a number by multiplying the field by 1.

 

[pbaldy]

What format is the text in? The multiplication may be confusing matters, as this works:

?cdate("16:30")
4:30:00 PM

You realize the Format() function is going to turn it back to text?

 

[jgabel]

Paul, thank you for your reply.

The field "start_time" is a text field in the table - here's an example of what it looks like: "0737 "

I noticed that the field has some blank spaces after 0737 that I might need to trim? Just trying to convert this to a time format like 07:37. Your correct in that I might be confusing matters by attempt to manipulate the field. I did not know that the Format function changes the field to text, yikes.

Do you have any suggestions for me to try?

Thanks again!!

 

[jgabel]

Paul, sorry to be dense, still learning Access, but it sounds like you want me to try the

?cdate("16:30")
4:30:00 PM

Can you help me with the expression for my field "start_time", I'm not sure how I should enter it.

Thanks again!

 

[pbaldy]

I was hoping your field had a colon in there so CDate would work alone. Without it we'll need some manipulation. You may need the Trim function to get rid of spaces, but with the resulting string:

?cdate(Mid("0737",1,2) & ":" & Mid("0737",3,2))
7:37:00 AM

I'm testing that in the VBA Immediate window, which is why you see the ? at the beginning, but the expression should work anywhere.

 

[jgabel]

Thanks once again! I copied the expression exactly as you have it, and retrieved 7:37 with the colon included.

The query pulls 7:37 for all records, how do I get it to reflect the actual start time? Here's what I have in the query expression so far:

start_time2: CDate(Mid("0737",1,2) & ":" & Mid("0737",3,2))

Where do I plug in "start_time" to this expression so it converts 0737 is hard coded for all records.

Thanks for being patient with my ignorance on this! I think I'm making it much more difficult than it actually is.

 

[pbaldy]

Sorry, I was using a hard coded value to test. For you try:

start_time2: CDate(Mid(start_time,1,2) & ":" & Mid(start_time,3,2))

 

[jgabel]

It works!! Thanks so much!! You da' man.

 

[pbaldy]

Happy to help!