Access short time - need leading zero

I have a time text box that needs to display the leading zero for short time (military time).  I have tried "hh:nn" (which Access changes to "short time") but it still drops the leading zero. I have even tried to change my system setting for displaying short time (I'm using Windows 7) but nothing I do seems to display the leading zero. ("Short time" actually displays without the leading zero in other applications such as Excel, although in Excel, I can successfully use "hh:mm"..)  The problem is that the input mask requires a 2-digit hour, but when users sees a single-digit hour displayed, they don't enter 2-digits.  One user tried to just change the second digit of the minutes, but got an error message (because the hour was a single-digit).  This is a bound text box.  Any suggestions will be welcomed.
KarenTaggartAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
The good news is ... I have the db.
That bad news is ... I *do* get the leading zeros.

It must be something specific on your system.  Did you check your Regional settings per the post @ http:#a35173112  ?

mx
Capture1.jpg
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" I have tried "hh:nn""

Humm.  Try   "HH:nn"   for 24 Hour time ...

And possibly get rid of the Input Mask ..

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This is what works for me .... see image.

mx
Capture1.gif
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I find Input Masks for Date and Time to be at best ... annoying.  I never use them ... as I do not see them to be necessary.  For example, in the image below ... For FIELD1 ... I just typed 9 colon 45  and it was reformatted to 09:45

For FIELD2 ... I only had to type 3-18 and it was formatted to 03-18-2011.  You never have to type the Year if it's the current year.

So ... to me, masks only add to confusion for dates ...

mx
0
 
KarenTaggartAuthor Commented:
I've tried "HH:mm" and no luck.  The user absolutely does not want to type the colon, so I pretty much have to use an input mask.  But just to test it, I removed the input mask and still did not get the leading zero.  I completely agree that changing the system time format SHOULD work, but it doesn't.  (Even in Excel or in the immediate window.)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, I'm kind of short in time here ...

ok ... just kidding.

Hummmm .... ok ... are you in the US ?  (as opposed to UK, etc)

How do you have Regional setting set ... per the image I posted ?

What is your Input Mask ... ?

mx
0
 
KarenTaggartAuthor Commented:
I'm in Oklahoma.  

My input mask is
      00:00;0;_

My regional setting is
 Short time regional settings
I wonder if this is a Windows 7 change?  I'm pretty sure this (this input mask and format to hh:nn) has worked in the past.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
weird.  I sure works on my system.

Side note ... if  you use this for a mask ...

90:00;;_

then the leading zero is not required.

Let me ping the Date/Time expert ... (cactus_data) ... who is in the UK.  Maybe he can shed some light on this ...

mx
0
 
KarenTaggartAuthor Commented:
Having trouble with the 90:00;;_ input mask - I'm unable to enter without 2-digit hour.  ??
0
 
KarenTaggartAuthor Commented:
OK - I know what I was doing.  That input mask works, but the user has to hit the space bar (or arrow) to get to the second digit position (or type the colon).  I'm not sure the users will do that.  (Dang users!  :o)  

I tried removing the input mask and formatting, then processing the input through code, but it's a date field and Access barfed on that, too, since "800" isn't a valid date or time.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You still have to Space to ... or click onto the 2nd position ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
gustav will be along soon ...

mx
0
 
Gustav BrockCIOCommented:
The format, HH:nn, seems correct, but the input mask should read:

#0:00,0,_

/gustav
0
 
KarenTaggartAuthor Commented:
I have tried that, too.  Like the one DatabaseMX suggested, this input mask works, but the user has to hit the space bar (or arrow) to get to the second digit position (or type the colon).  The  users won't remember to do that and will wind up with the input mask error.
0
 
Gustav BrockCIOCommented:
Well, the purpose of an input mask is to force some distinct user behaviour to ensure some decent input that will not fail.

If you don't want this enforcement, remove the input mask and let the user input in free-style. As long as some type of date/time format is applied to the textbox, values entered that cannot be read as a date/time will raise an error. Trap this, asking the user to input a valid date/time expression.

/gustav
0
 
KarenTaggartAuthor Commented:
The issue isn't the input mask.  We want them to put in a 2-digit hour. The issue is the formatting.  No matter what I try, the "short date" format drops the leading zero.  This display of a 1-digit hour causes problems when the user edit.  Seeing a 1-digit hour, he/she assumes that's a correct way to input.  One user simply tried to change on of the digits of the minutes and (because it was displaying a 1-digit hour), got an input mask error.  What I want to do is line up the format and the input mask so the users always see a 2-digit hour.

At this point, I've added another field to the table - a text field.  That's what the user will enter the time into.  I trap the input string, put a colon in if one isn't there, check for valid values (hour 0-23, minute 0-59(, format it the HH:mm and put it in both the text-input box and the actual time field.  Seems like a lot of work for a snarky time format, but it's working.
0
 
Gustav BrockCIOCommented:
I see.
However, using the format HH:nn here, time is displayed nicely with a leading zero and I haven't seen anything else so I'm out of ideas.

/gustav
0
 
KarenTaggartAuthor Commented:
Thanks for trying.  I've been using this HH:nn off and on for a long time and don't ever remember having an issue.  Even when I change my system short time to show a leading zero, it gets dropped.  It does that even in Excel, but in Excel I can create a custom format (HH:nn) - Access won't let me.  (Or I can't find it!)

Thanks again
0
 
Gustav BrockCIOCommented:
Perhaps it doesn't work when AM/PM are set?
We use the 24 hour clock here.

/gustav
0
 
KarenTaggartAuthor Commented:
My system clock is showing AM/PM, but the regional settings (control panel) have options for short time, long time, etc.  Maybe it has to do with regions?

But this will be deployed on different users' computers, so I can't rely on them changing a system setting.  I'll go with using two fields and controlling the format in the displayed text box.  I suppose I could just use a text field (instead of a date field) and control it that way - this particular  time won't be used in any calculations anywhere.

I pulled out an old computer last night and tried it on windows XP just to see if it's a Windows 7 thing, but got the same thing (dropping the leading zero).  

This SHOULD have been simple!  :o)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"and I haven't seen anything else so I'm out of ideas."
Now THAT ... is scary!

Karen (?) ... you are definitely doing everything right.  Something very odd is going on here.

"My system clock is showing AM/PM, but the regional settings (control panel) have options for short time, long time, etc.  "

Ok ... but do you have the specific Format enter as I showed @ http#:a35168920 ?  Seems almost impossible that your system tray clock does not show in 24 hr format.  Mine does.  In fact, all the clocks that I have (even cell phone) that can be set to 24 hr ... are.  Even at work.  I'm definitely a 24 hr guy - even in the US.  It's unambiguous.

Did we ever confirm that the Field in your table this Control is bound to ... is a Date/Time data type ?

mx

0
 
KarenTaggartAuthor Commented:
Yes, the field is bound to a date/time field.  (Or was... I changed it to a text field so I can control the formatting and when I need to calculate the elapsed time between two events, I convert to date/time.)

It doesn't seem reasonable to me that I can't set my system clock to military time, but I sure can't find where to do that.  I can find where to set each specific date/time format, but not how to change the format for the system clock.  Nothing that looks like your screen capture where I can say which time format to use.  I'm semi-new to Windows 7, so maybe I am overlooking something.

0
 
KarenTaggartAuthor Commented:
I Googled how to make a Windows 7 system clock display military time.  I laughed out loud at the answer - change "long time" to HH:mm...  Ah, Microsoft...

http://freewindowsvistatutorials.com/showMilitaryTime24HoursInSystemClockInWindows7.php
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
opps .. Windows 7 ... let me look in my Win7 system ...

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Win7:

Control Panel >> Region and Language >> Formats Tab >> Additional Settings ... Time Tab

see image ...

mx
Capture1.jpg
0
 
Gustav BrockCIOCommented:
OK. I'm running Vista which is not that fancy.

/gustav
0
 
KarenTaggartAuthor Commented:
Well, at least I know I'm not just missing some obvious setting.  However, two things - first, even when I did that, Access overrides it with a 1-digit hour format and second, I can't control my users' system settings.  :o(  

So I guess it's stay with the text field, control the format, and convert to date/time if I need to use it in a calculation.

Thanks, guys, for all your input!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"So I guess it's stay with the text field,"
I would *not* do that. Since these are real times, I would take advantage of the Date/Time data type.

Can you upload a db that exhibits this behavior.  I would like to see what it does on my Win 7 machine (Access 2010)

?

mx
0
 
KarenTaggartAuthor Commented:
The one I'm working on is huge and complex. Let me put a sample together and get that to you.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok ... be sure the sample does exhibit the issue :-)

I will be out for a while now ...

mx
0
 
KarenTaggartAuthor Commented:
I pulled the form, subform, and table out of the actual database, commented out some code so it would open.  I also added a "real time field" that's a date/time field.  The "TimeNotified" field is the text field.  You can take a look at how I'm processing the input to display it the way I need to.  You can enter (in the text field) with or without a colon, with 3 digits or 4. The code checks to be sure the hour and minutes are valid, then formats to HH:mm.  The real date field does exhibit the formatting issue in this sample database.

The time fields in the database log when a stem cell product is packed or received, and (in this form) when someone is notified that there's an anomoly. There's one place I need to use the actual time - to calculate the amount of time between its being packed and received. In that code, I convert the text to date and the calculation still works.  In all other instances, the time just needs to display and print.

I really do appreciate you digging into this!

Sample-031911.zip
0
 
KarenTaggartAuthor Commented:
FYI - I'm using Access 2007, not 2010.  I expect that you will still see the formatting issue, but I look forward to your feedback!
0
 
Gustav BrockCIOCommented:
Same result here (Vista and Access 2010):

Date Notified      Time Notified      Real Time Field
03-19-2011      09:00      09:00
03-19-2011      07:45      07:45
03-19-2011      18:00      18:00

/gustav
0
 
GRayLCommented:
XP Pro - Access 2003.  System date set to yyyy-mm-dd.   In the Immediate Pane:

? #15-3#
2011-03-15
? #3-15#
2011-03-15
? #3:15#
03:15:00

Karen:what are your results doing the same?

Ray
0
 
KarenTaggartConnect With a Mentor Author Commented:
XP Pro and Access 2003 gave the same result in the form, but I need to boot that computer up to see what the immediate pane shows.  

Based on all the great feedback from you guys, I am certain it has to do with the Windows regional settings.  If I change the format for "long time" in Windows, it does display a 2-digit hour.  My client contact is running Vista and Access 2007 and also uses a computer from the client (XP Pro and Access 2003) and she was seeing the single-digit hour when I had a true date/tiime field, the short time input mask, and the short time format (on both computers).  I am not going to be able to control the end-users' system settings, so I'm going to go with the text field and convert to date/time if or when I need to calculate using that field.  It's annoying to have to do that, but I can't let the users' format display a 1-digit hour when 2-digits are required by the input mask.  At least (with all your help) I know why it's doing that.  

I appreciate your help!!

0
 
KarenTaggartAuthor Commented:
The symptom I needed to address involved regional system settings, which I can't control on my users' computers.  So there wasn't an actual "fix" to make the formatting work, but knowing why it behaved that way was very valuable.  My solution was to change the date/time field to text so I can trap the input, check for a valid time, and format it the way it needs to be displayed.  If/when I need that value to be a true date/time for calculations, I will convert it in code before the calculation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.