My time field has extra zeros

[Lkwdmntr]

Hi, I have a time field that I pulled in from a table. when I run the query it gives me 2 extra zeros. ie: 7:00:00 AM

Not sure how to fix this. The fields name is StartTime and I tried StartTime:Format([StartTime], "hh:mm AM/PM") with no success.

Any help would be appreciated.

 

[theDBguy]

Hi. You could try:

Start_Time:Format([StartTime], "hh:nn AM/PM")

 

[Lkwdmntr]

Hi. You could try:

Start_Time:Format([StartTime], "hh:nn AM/PM")

is that supposed to be an "nn" or is that a typo?

 

[theDBguy]

is that supposed to be an "nn" or is that a typo?

Not a typo, it's a lower case N. Did you try it? Just curious...

 

[The_Doc_Man]

Just by way of explanation, if you got hh:nn:ss AM/PM as a format, it was probably due to your computer's regional settings selecting that format. And the reason it is "nn" for minutes is because it is "mm" for months. Using an explicit FORMAT function overrides the regional settings.

 

[Isaac]

You can also use: h:mm AM/PM

 

[isladogs]

Although Isaac's suggestion does work, in my opinion it would be better to use e.g. h:nn AM/PM
Here are some examples

?Format(Time, "h nn AM/PM")
8 27 AM
?Format(Time, "h mm AM/PM")
8 27 AM
?Format(Time, "nn AM/PM")
28 AM
?Format(Time, "mm AM/PM")
12 AM
?Format(Time, "mmm")
Dec

I'm not sure what is going on with the last 2 examples but it certainly isn't what you'd expect!

 

[onur_can]

There is an error in your spelling. It should be "nn" instead of "mm". Also, there should not be any spaces between AM / PM expressions.

 

[gemma-the-husky]

obviously the extra zeroes represent seconds, so the default format is hh:nn:ss - so to get something else, you need a slightly different format.

 

[isladogs]

Going back to post #1, I forgot to say that
1. you can format the field as Medium Time in the table giving displayed value like 11:07 AM, 06:15 PM
2. you can't use the same name StartTime for a query field alias as the field name itself. Notice how DBG modified it in post #2

 

[onur_can]

Friends, I explained the solution to the problem with images. I think this is the solution. I think there will be no problem if the Lkwdmntr friend applies this solution.

 

[Lkwdmntr]

Wow! I just want to thank everyone for their responses. I certainly better understand the formatting process. I ended up using
Time: Format([StartTime],"h:nn AM/PM")
Worked like a charm. Thanks again.

 

[isladogs]

We were all pleased to help. Good luck with your project

 

[theDBguy]

Wow! I just want to thank everyone for their responses. I certainly better understand the formatting process. I ended up using
Time: Format([StartTime],"h:nn AM/PM")
Worked like a charm. Thanks again.

Hi. Glad to hear you got it sorted out. Just FYI, "Time" is a *reserved* word in Access. You might consider using a different name for your column to avoid any issues in the future.

Good luck with your project.

 

[gemma-the-husky]

Wow! I just want to thank everyone for their responses. I certainly better understand the formatting process. I ended up using
Time: Format([StartTime],"h:nn AM/PM")
Worked like a charm. Thanks again.

you want "hh" rather than just "h", don't you?

 

[isladogs]

you want "hh" rather than just "h", don't you?

That depend whether the OP wants 06:35 AM rather than 6:35 AM. Times such as 10:25 AM will work with h or hh

 

[gemma-the-husky]

I didn't realize that just "h" would work,

 

[isladogs]

Yup. Using e.g. hh just ensures two digits are always shown including a leading zero where appropriate
For example

?Format(Time,"h:n:s")
20:7:18

?Format(Now,"d m yyyy h:n:s")
25 3 2021 20:9:10

But here's two odd ones. See what its doing with the year?

?Format(Now,"d m y h:n:s")
25 3 84 20:11:37

?Format(Now,"d m yyy h:n:s")
25 3 2184 20:11:54

 

[Isaac]

?Format(Now,"d m y h:n:s") 25 3 84 20:11:37

y by itself is the day of the year. 1-366 according to MS.

 

[isladogs]

Hi Isaac.
Yes that is of course correct ... but it isn't necessarily obvious to users who mistakenly type e.g yyy instead of yy or yyyy