Solved

Access short time - need leading zero

Posted on 2011-03-18
36
2,438 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:KarenTaggart
  • 16
  • 13
  • 6
  • +1
36 Comments
 
LVL 75
ID: 35168900
" I have tried "hh:nn""

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

And possibly get rid of the Input Mask ..

mx
0
 
LVL 75
ID: 35168920
This is what works for me .... see image.

mx
Capture1.gif
0
 
LVL 75
ID: 35168978
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
 

Author Comment

by:KarenTaggart
ID: 35169046
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
 
LVL 75
ID: 35169068
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
 

Author Comment

by:KarenTaggart
ID: 35169088
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
 
LVL 75
ID: 35169113
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
 

Author Comment

by:KarenTaggart
ID: 35169146
Having trouble with the 90:00;;_ input mask - I'm unable to enter without 2-digit hour.  ??
0
 

Author Comment

by:KarenTaggart
ID: 35169166
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
 
LVL 75
ID: 35169167
You still have to Space to ... or click onto the 2nd position ...

mx
0
 
LVL 75
ID: 35169174
gustav will be along soon ...

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35169661
The format, HH:nn, seems correct, but the input mask should read:

#0:00,0,_

/gustav
0
 

Author Comment

by:KarenTaggart
ID: 35169868
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35171428
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
 

Author Comment

by:KarenTaggart
ID: 35171511
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35171571
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
 

Author Comment

by:KarenTaggart
ID: 35171603
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35171710
Perhaps it doesn't work when AM/PM are set?
We use the 24 hour clock here.

/gustav
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:KarenTaggart
ID: 35171741
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
 
LVL 75
ID: 35172472
"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
 

Author Comment

by:KarenTaggart
ID: 35173058
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
 

Author Comment

by:KarenTaggart
ID: 35173088
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
 
LVL 75
ID: 35173090
opps .. Windows 7 ... let me look in my Win7 system ...

mx
0
 
LVL 75
ID: 35173112
Win7:

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

see image ...

mx
Capture1.jpg
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35173343
OK. I'm running Vista which is not that fancy.

/gustav
0
 

Author Comment

by:KarenTaggart
ID: 35173400
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
 
LVL 75
ID: 35173435
"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
 

Author Comment

by:KarenTaggart
ID: 35173519
The one I'm working on is huge and complex. Let me put a sample together and get that to you.
0
 
LVL 75
ID: 35173533
ok ... be sure the sample does exhibit the issue :-)

I will be out for a while now ...

mx
0
 

Author Comment

by:KarenTaggart
ID: 35173603
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
 

Author Comment

by:KarenTaggart
ID: 35173606
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 35174098
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35174931
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
 
LVL 44

Expert Comment

by:GRayL
ID: 35176389
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
 

Assisted Solution

by:KarenTaggart
KarenTaggart earned 0 total points
ID: 35176578
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
 

Author Closing Comment

by:KarenTaggart
ID: 35213313
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now