Convert Text String to Medium Time Value (1 Viewer)

nyaworld · Mar 23, 2016

Here's my situation:

I have a back end SQL table that I access via Access Web App/SharePoint and an Access 2013 Front End that I use to create reports, forms, etc.

I have a linked table with time fields in it, [Start Time] and the properties are set to Short Time. When I open up this linked table in my FE, the [Start Time] shows text string 17:30:00.000 instead of 5:30 PM.

Now I'm using that field in a form and all the times are showing text string and formatting the field in the properties does nothing.

How can I display this text string to a medium time format?:banghead:

pbaldy · Mar 23, 2016

You can use the CDate() function on it, though if memory serves you may need to trim off the decimal portion at the end.

nyaworld · Mar 23, 2016

pbaldy said:
You can use the CDate() function on it, though if memory serves you may need to trim off the decimal portion at the end.

Would you be so kind as to share the syntax for this?

For example the current text value is 17:30:00:000 (5:30pm), how would I trim off and then convert this text to a time ?

I have tried this and it doesn't work in a query:

=CDate([Start Time)

Is it supposed to be in my query, or in the control property in my form?

pbaldy · Mar 23, 2016

As I mentioned, I think you'll have to trim off the decimal portion.

nyaworld · Mar 23, 2016

pbaldy said:
As I mentioned, I think you'll have to trim off the decimal portion.

How would I trim off the decimal portion?

MarkK · Mar 23, 2016

If it's a string you can use Split(), which returns an array of strings, splitting your original string at a delimiter you provide, so consider code like this . . .

Code:

debug.print split([Start Time], ".")(0)

[COLOR="Green"]'or to be more verbose . . .[/COLOR]
dim var
[COLOR="Green"]'split the string into an array as delimited by the "." character[/COLOR]
var = split([Start Time], ".")
[COLOR="Green"]'print the first array element to the immediate pane[/COLOR]
debug.print var(0)

This splits your string at the ".", and returns the first array element, which would be "17:30:00".

nyaworld · Mar 23, 2016

I wanted to make any adjustments into the query that is feeding my form.

How do I combine these two formulas into one so I can write the formula into a new column and use that field in my form:

Original problem : [Start Time] = 17:30:00.000 <----text value

StartTimeNEW: Mid([Start Time],1,5)

then I create a new column based on StartTimeNew:

StartTimeFix: CStr(Format(CDate(Left([StartTimeNEW],2) & ":" & Right([StartTimeNew],2)),"hh:nn AM/PM"))

************Never mind. I used this in my control source and it worked!

=CStr(Format(CDate(Left([Start Time],2) & ":" & Right([Start Time],2)),"hh:nn AM/PM"))

This took 17:30:00:000 and made it show up in my form as 5:30pm . That's what I needed.

Thank you all for your help.

MarkK · Mar 23, 2016

This looks cumbersome to me . . .

nyaworld said:
StartTimeFix: CStr(Format(CDate(Left([StartTimeNEW],2) & ":" & Right([StartTimeNew],2)),"hh:nn AM/PM"))

Why not just do . . .

Code:

StartTimeNEW: CDate(Left([Start Time],5))

. . . and apply formats later.

pbaldy · Mar 23, 2016

I'll get out of the way.

nyaworld · Mar 24, 2016

MarkK said:
This looks cumbersome to me . . .

Why not just do . . .

Code:

StartTimeNEW: CDate(Left([Start Time],5))

. . . and apply formats later.

It shows an extra couple of zeros. How would I get rid of the seconds?

OLD -> NEW
Start Time StartTimeNEW
15:30:00.000 3:30:00 PM

MarkK · Mar 25, 2016

nyaworld said:
It shows ...

What is "it?" I would apply formats in your final presentation medium, like a report, so if this is a query, leave it as-is, or in a report or form, format the control that displays the data.

nyaworld · Mar 25, 2016

MarkK said:
What is "it?" I would apply formats in your final presentation medium, like a report, so if this is a query, leave it as-is, or in a report or form, format the control that displays the data.

By "It", I mean the result of field in my form.

Thank you, the formatting did work.

Convert Text String to Medium Time Value (1 Viewer)

nyaworld

Registered User.

pbaldy

Wino Moderator

nyaworld

Registered User.

pbaldy

Wino Moderator

nyaworld

Registered User.

MarkK

bit cruncher

nyaworld

Registered User.

MarkK

bit cruncher

pbaldy

Wino Moderator

nyaworld

Registered User.

MarkK

bit cruncher

nyaworld

Registered User.

Similar threads

Users who are viewing this thread