Time format hhnn drops leading zero

[Chuck]

In Access 2007 with SP 2, I have a Text Box with a Date/Time Data Type
Control Source. Setting the Format property to "hhnn" or "hnn" both give me
the same results: there is no leading zero on the time part of the displayed
value when the time of day is before noon. Interestingly enough, midnight is
displayed as "000".

Would anyone happen to have a work around for this problem?

 

[Arvin Meyer [MVP]]

What happened to the colon:

?Format(#7:35#,"hh:nn AMPM")
07:35 AM

 

[Chuck]

The colon is optional when setting the Format property for a Text Box.
Besides, the result is the same with or without a colon. Times before noon
are displayed without the leading zero. The Help file for the Date/Time Data
Type format says:

h Hour in one or two digits, as needed (0 to 23).
hh Hour in two digits (00 to 23).

 

[Arvin Meyer [MVP]]

According to my tests in the immediate window:

?Format(#7:35#,"hhnn")
0735

?Format(#7:35#,"hhnn AMPM")
0735 AM

?Format(#7:35#,"hh:nn AMPM")
07:35 AM

Check your settings in the Regional and Language Options applet in the
Control Panel to make sure Windows will display properly. Even then,
formating as above should override them.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Chuck]

Sure does. The result you obtained in your first test is exactly what I want.

However, your test uses the VBA Format function. My problem is with the
Format property of a Text Box control. They should produce the same results
(from the same input value), but I'm afraid the operative word here is
"should".

Incidentally, when I said above that "times before noon are displayed
without the leading zero.", I should have said "times before 10:00 am".

The problem appears to me to be that the software functionality doesn't
agree with the documentation of that functionality. The documentation very
clearly says that one h will cause the time to be formatted as 0 to 23 (with
no leading zeroes before 10:00 am) while two h's will cause the time to be
formatted as 00 to 23 (with leading zeroes).

I have checked the Windows Regional and Language Options and the default
values (for Vista) are just fine. Besides, as you said, formatting should
override them. And, in fact, it is overriding the date format option by
formatting the time on a 24 hour basis.

 

[Arvin Meyer [MVP]]

The format is probably being converted to "Short Time"

You can change the Input Masks from the Access table properties to alter how
short time displays.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Dudhley d'Urite]

I discovered a workaround at:

https://www.pcreview.co.uk/forums/date-format-hh-nn-doesnt-force-leading-zero-t2624834.html

that is NOT dependent on the system time display settings and works for reports in Access 2010 (32-bit). If your control source field is named [Date_Field], rename the CONTROL (e.g, from "Date_Field" to "Date_Field_cntl") and then add the formatting directly to the control source:

=Format([Date_Field],"yyyy-mm-dd hh:nn:ss")

Of course, I would prefer that Microsoft fix this bug in the Report!Format method so that "yyyy-mm-dd hh:nn:ss" worked as advertised.

 

[fchs]

The Format option is no good you need to enter data in the control. Using 3 h's will display a leading zero

hhh:nn:ss

 

[Gilad]

fchs, your method only causes the hours to repeat and appear doubled.

Dudhley,
Your method only works if you do not need to edit the field. If you are working on a form it will not work.
It is actualy a problem for me. I have set both input mask and format for a particular field. Now if the field has a date and time value, it can no longer be easily edited. Changing any digit in the date or time brings up a message indicating that the entered data is not in accordance with the input mask. The time displays only one digit for the hours, but the input mask requires two. I tried using an input mask that allows for not inputing a digit (either 9 or # mask values) but still the input mask requires that the user enters two digits for the hours even if in only has one.

Here are the properiteis I have set for the form's text box control:
format:dd/mm/yyyy hh:nn
input mask: 90"/"90"/"0000" "90:00;0;"_"

The only workaround I have found was to require the user to type in double digits for the hour value even if she is only changing some other digit in the date value.