Format Property bug: "hh" doesn't display leading zero

[kamrhein]

A number of closed topics deal with problems displaying military time
with various suggested solutions, but none of the discussions I have
seen mention that the the underlying problem seems to be a bug in
Access' Format Property.

I was unable to find a discussion of this problem in the Microsoft
Knowlege Base.

Military time (and many scheduling and dispatching applications) always
displays 24 hours with a leading zero for hours less than 10, e.g.,
"08/17/2006 05:35"

Contrary to its documentation, the Microsoft Access Format Property
does not display a leading zero for times with hours less than 10.

Reference: Microsoft Access Help Topic: "Format Property -
Date/Time Data Type".

"hh Hour in two[sic] digits (00 to 23)."

To demonstrate the issue:

1. Create a new blank database.

2. Create a new unbound form.

3. Add an unbound Text Box.

4. Set it's Format Property to "mm/dd/yyyy hh:nn".

5. Waste the next hour zorking around trying to get it to display
military time for hours less than 10. (OK, go ahead and bind it to a
Table Date/Time Field. Waste two hours. Fool with the Input Mask
Property. Waste three hours.)

This Format Property issue also applies to Reports, but can be kludged
by crafting a calculated Control with the Control Source set to an
expression calling the Format Function with the same format string
(=Format([MyDateTime], "mm/dd/yyyy hh:nn")), which works as
documented, but this technique is useless for Form data-entry Controls
that need to display military time.

 

[kamrhein]

(e-mail address removed) wrote:

[Just trying to see how this lame word-wrapping works. And why does it
use my e-mail address as my identifier when most everybody else has
their nickname?]

A number of closed topics deal with problems displaying military time
with various suggested solutions, but none of the discussions I have
seen mention that the the underlying problem seems to be a bug in
Access' Format Property.

I was unable to find a discussion of this problem in the Microsoft
Knowlege Base.

Military time (and many scheduling and dispatching applications) always
displays 24 hours with a leading zero for hours less than 10, e.g.,
"08/17/2006 05:35"

Contrary to its documentation, the Microsoft Access Format Property
does not display a leading zero for times with hours less than 10.

Reference: Microsoft Access Help Topic: "Format Property - Date/Time
Data Type".

"hh Hour in two[sic] digits (00 to 23)."

To demonstrate the issue:

1. Create a new blank database.

2. Create a new unbound form.

3. Add an unbound Text Box.

4. Set it's Format Property to "mm/dd/yyyy hh:nn".

5. Waste the next hour zorking around trying to get it to display
military time for hours less than 10. (OK, go ahead and bind it to a
Table Date/Time Field. Waste two hours. Fool with the Input Mask
Property. Waste three hours.)

This Format Property issue also applies to Reports, but can be kludged
by crafting a calculated Control with the Control Source set to an
expression calling the Format Function with the same format string
(=Format([MyDateTime], "mm/dd/yyyy hh:nn")), which works as documented,
but this technique is useless for Form data-entry Controls that need to
display military time.

 

[Rick Brandt]

A number of closed topics deal with problems displaying military time
with various suggested solutions, but none of the discussions I have
seen mention that the the underlying problem seems to be a bug in
Access' Format Property.

I was unable to find a discussion of this problem in the Microsoft
Knowlege Base.

Military time (and many scheduling and dispatching applications)
always displays 24 hours with a leading zero for hours less than 10,
e.g., "08/17/2006 05:35"

Contrary to its documentation, the Microsoft Access Format Property
does not display a leading zero for times with hours less than 10.

Reference: Microsoft Access Help Topic: "Format Property -
Date/Time Data Type".

"hh Hour in two[sic] digits (00 to 23)."

To demonstrate the issue:

1. Create a new blank database.

2. Create a new unbound form.

3. Add an unbound Text Box.

4. Set it's Format Property to "mm/dd/yyyy hh:nn".

5. Waste the next hour zorking around trying to get it to display
military time for hours less than 10. (OK, go ahead and bind it to a
Table Date/Time Field. Waste two hours. Fool with the Input Mask
Property. Waste three hours.)

This Format Property issue also applies to Reports, but can be kludged
by crafting a calculated Control with the Control Source set to an
expression calling the Format Function with the same format string
(=Format([MyDateTime], "mm/dd/yyyy hh:nn")), which works as
documented, but this technique is useless for Form data-entry Controls
that need to display military time.

A workaround would be to place a TextBox on top of your editable one that has
the Format() function in its ControlSource to give you the appearance you want.
In the GotFocus event set focus to the editable TextBox behind the non-editable
one. Since formatting is lost when a control has focus anyway the result should
be identical to a TextBox where the format property worked as desired.

 

[Brendan Reynolds]

Seems to work as documented for me. Tested in both Access 2003 and Access
2000. Here's the result ...

http://brenreyn.brinkster.net/format.jpg

 

[Rick Brandt]

Seems to work as documented for me. Tested in both Access 2003 and
Access 2000. Here's the result ...

http://brenreyn.brinkster.net/format.jpg

You made me look Brendan. Apparently if your regional settings in Control Panel
are set up with hh: instead of h: then it works. Without that then (Access 97
at least) definitely ignores the leading zero on the hour even when the format
property calls for it.

 

[Brendan Reynolds]

Thanks Rick. I can reproduce that in Access 2000, but not in Access 2003. So
it looks like it may have been fixed in either Access 2002 or Access 2003. I
don't have an installation of Access 2002 available to test.

My tests also involved two different versions of Windows. Access 2000 is
running on Window 98, Access 2003 on Windows XP SP2. So I can not rule out
the possibility that the problem may be related to the OS version.

 

[BruceM]

I noticed that with h: in the regional settings the format is ignored, but
you can apply the format h:nn even if hh: is the default. It works one way,
but not the other. An, um, oversight, I guess.

 

[Rick Brandt]

Thanks Rick. I can reproduce that in Access 2000, but not in Access
2003. So it looks like it may have been fixed in either Access 2002
or Access 2003. I don't have an installation of Access 2002 available
to test.
My tests also involved two different versions of Windows. Access 2000
is running on Window 98, Access 2003 on Windows XP SP2. So I can not
rule out the possibility that the problem may be related to the OS
version.

Good point. I only have A97 here at home, but I am running WinXP SP2 so (for
A97 at least) the newer OS doesn't fix the problem.

 

[Brendan Reynolds]

Thanks again, Rick. Probably not an OS issue then.

It would be interesting to hear if anyone can reproduce the problem using
Access 2002 or Access 2003.

 

[kamrhein]

Bruce, Rick, Brandon:

I changed my Regional Settings to "hh:" and voila, and it was
displaying "08/17/2006 01:45" when I switched back to the Access
window.

I had the problem running Access 2000 on Windows 2000.

Now I'm going to have to figure out the implications of changing the
default. I'm under the impression that the whole point of the Format
Property is to override the default.

Thanks for your solution to the mystery!

[Any idea why my identifier is my e-mail address?]

 

[Joan Wild]

It fails for me in 97 2000 2002 2003 and the beta.
Regional Settings h:nn
Format property hh:nn
Displays 9:11 not 09:11

I filed this as a bug in the beta, which they acknowledged and said it would
not be fixed in this version.

 

[BruceM]

You should be able to override the default by applying a format. For
instance, you can format hours as a single digit even if the default is
2-digit. I hesitate to label apparent anomalies as bugs, but this could
well be a bug. If you change the default that format will apply to any time
fields to which you have not applied formatting.
For the other question about the identifier, if you are using a newsreader
such as Outlook Express there is a setting in there. In OE6 you would go to
Tools > Accounts. Click the News tab, click the account name to select it,
and click Properties. Click the General tab, and see what is in the Name
box. That will be the identifier used in the group, I believe. If it is
blank, I suppose it would default to the e-mail address, which you should
spoof in any case since it is part of the message.

 

[Brendan Reynolds]

I just reproduced it, in Access 2003, on the very same system on which I
could not reproduce it earlier. I'm afraid I have no idea what I did
differently this time. Well, there is one thing I did differently. I
experimented with changing the case, I changed 'H' to 'h'. But even after
changing it back, I can still reproduce the bug now.

 

[kamrhein]

I implemented Rick Brandt's suggestion (thanks Rick) to put a properly
formatted [with the Format Function] TextBox on top of the editable
one, and it worked just fine, but the difference between the editable
display and the Input Mask (All DateTimes WILL have an input mask!)
caused no end of user confusion, so I blew it off.

For what it's worth:

Format Function for Visual Basic 6.0 Users
msdn2.microsoft.com/en-us/library/wb216dct.aspx

User-Defined Date/Time Formats

and

Format Function Changes in Visual Basic (Visual Basic .NET)
msdn.microsoft.com/library/en-us/vbcn7/html/vaconFormatChangesInVBNET.asp?frame=true


Visual Basic Language Concepts

Format Function Changes in Visual Basic

Date/Time Format

both have:

Visual Basic 6.0

"The characters "H[sic]h" display the hour as a number with leading
zeros,..."


The Microsoft Office Access Help Topic "Format Property - Date/Time
Data Type" makes no reference to an upper-case "H" format symbol, and
any attempt to use it in the Format Property string results in it being
changed to an lower-case "h", which, as has been demonstrated, behaves
unexpectedly when used in "hh".


This same Topic also makes the statement:

"Custom formats are displayed according to the settings specified in
the regional settings of Windows. Custom formats inconsistent with the
settings specified in the regional settings of Windows are
ignored[sic]."

This is somewhat bizarre in that the specific purpose of the Custom
Formats provided by the Format Property is to override the default
format specified in regional settings and make them "inconsistent with
the settings specified in the regional settings of Windows".

 

[Brendan Reynolds]

About the uppercase 'H' vs. lowercase 'h' - that's in the Control Panel
Regional Options time format, not in the Access Format property or the VBA
Format function. My apologies if I did not make that clear.

 

[Rick Brandt]

I implemented Rick Brandt's suggestion (thanks Rick) to put a properly
formatted [with the Format Function] TextBox on top of the editable
one, and it worked just fine, but the difference between the editable
display and the Input Mask (All DateTimes WILL have an input mask!)
caused no end of user confusion, so I blew it off.

Ah well that's different. I consider InputMasks the work of the devil and
(almost) never use them.

 

[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.