Fitness 58
By Richard Rost
2 months ago
Convert 4-Digit Military Time to Standard 12-Hour Time
In this Microsoft Access tutorial, I will show you how to let users enter 24-hour time (Military Time) directly into a form and have it automatically convert to a time format that Access prefers. We will learn how to handle both short entries like "9" for 9 AM and four-digit entries like "1745" for 5:45 PM, making data entry faster and more flexible. We'll also talk about how to format these values properly and deal with some common edge cases, all within the context of my Fitness Database series. This is part 58.
Members
In the extended cut, we will add a checkbox option that lets you choose whether to keep or overwrite the time when copying items from a previous day using the Copy Item button. I will show you how to make the checkbox functional so copied items can either retain their original time or use the current time, depending on your selection.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, 24-hour time, military time, convert text box time to standard, auto format time input, time tracking database, work log time entry, parse numeric time input, VBA time conversion, handle edge case times, enter time without colon, format hours and minutes, food log time field, Access default time field, copy item keep time checkbox, Format(n, "00") example
Subscribe to Fitness 58
Get notifications when this page is updated
Intro
In this video, you'll learn how to let users type in 24-hour or military time directly into a text box in your Microsoft Access database, and have it automatically convert to a format Access recognizes. I'll show you how to handle input like 700 for 7 AM, 1500 for 3 PM, or even just 9 for 9 AM, all without requiring a colon. You'll see how to split numeric input into hours and minutes, format minutes with leading zeros, and use techniques like integer division, modulus, and Select Case statements to process and validate different time entry styles. This is part 58.
Transcript
In today's video, I'm going to teach you how to type in 24-hour time, also called Military Time, into a field in your Microsoft Access database and have it automatically convert over to a time that Access likes.
It doesn't like it when you type in 700, but if you type in 700 into my text box, you'll get 7 o'clock AM. I'm also going to show you how to make it so that if you just type in a simple number like 9, it'll assume 9 AM. You don't have to actually type in the 9 A or the 9 P. If you type in 15, it'll assume 3 PM. So we can even make it easier than Military Time because I do that a lot. Sometimes I just want to type in 6, enter, and have that assume it's 6 AM.
So even if you've watched my previous video on Military Time, that's a new trick that we're going to be doing today. The tricks that I'm covering are in my Fitness Database series. This is part 58.
Do you have to watch all the previous videos? No, you don't. And do you have to care about Fitness? No. I don't care if you don't want to track calories and protein. Don't worry about it. The point of this database series is I'm building this database for myself and I'm tossing in all kinds of cool tips and tricks to teach you guys how to do this stuff. So even if you're not building the Fitness Database, you can probably use this in, like, a Time Tracking Database or a Work Log Database or whatever, especially if you deal with a lot of 24-hour time.
So are you ready? Here we go.
One of the requests that I've gotten from all of you fine students is a few people have asked me if they could enter in 24-hour time up here without typing in the colon. They want to be able to type in, like, 20 hundred like that. Right now the system, if it doesn't see a valid date/time value, just ignores it and puts the current date/time into it, how we've got it programmed. So I want it so that if you type in, like, 2300, it puts in 11 PM.
I find this a lot myself, like I'll just put in nine and I want it to put in 9 AM, for example, or 11, I want 11 AM without having to type in the A or P. It's just little minor modifications like that that sometimes make a database really useful.
Now I do have another video, it's about three years old, where I teach how to be able to enter stuff like this for some different purposes. We do it in a clock-in/clock-out kind of thing. So check this video out if you want to learn a little bit more. But I'm going to go over the basics today and add a little bit of extra flair to it as well.
I'm going to show you how to implement this here because this box here, that's our default time box. This doesn't have any before update validation in it. These guys do, so these are, this would be a lot more complicated. I'm going to show you how to do it with this box, which honestly, this is the only one that I usually update the time in because I set the time first and then I put my items in and it just grabs the time off of that. I almost never come in here and change these times. Again, I'm just speaking from my experience with this database, but if enough of you want to see how to do it with these, then let me know. If enough of you post a comment, we'll do something.
All right, squeaky wheel gets degrees. So this guy, our default food time text, the after update event is pretty simple and straightforward. It says if it's not a valid date, then set this equal to now minus the date, which basically is the time. If you take now, which is the date and time, and subtract the date, you get just the time. Then default food time text equals format food log time, then we're formatting it properly.
So before all of that runs, we can check for numbers. If we have just a number, then we can convert that number over to a valid time format. If it is numeric, I'm just going to copy because I don't want to keep retyping this. If it's numeric, then let's start by checking for simple numbers like seven for 7 AM or 23 for 11 PM. Let's make a variable called L. Dim L as long. Sometimes it's x, sometimes it's L. It's just random.
L equals, now since we know it's a number at this point, we can convert it to a long — CLNG. If they type in a number with a point, a decimal point, no. I mean, I guess we could do that later if you want to type in 7.5 and have that convert to 7:30. I have had people want to do that before. Usually it's where they're adding stuff up, like adding times up, you get a time sheet from an employee and they spent two and a half hours on this, three and a quarter hours on that. You want to add them all up and then convert that to a valid number of hours and minutes. I have a whole separate video on that one too. I don't know where it is. Search my website or post a comment down below and I'll find it.
At this point, we know it's a valid number, so we can convert it to a long — CLNG, that guy. Now if L is less than 24, then they did what I want to do — just type in nine, for example. Then we're going to assume a simple time, like 7 for 7 AM. Here we can just simply tack on the minutes on the end of it.
Default food time text equals L & ":00" because we're still dealing with a text value. We're going to rewrite this text thing and then pass it to this, which will convert it to a proper time.
Otherwise, else, actually just do an else. If L is greater than or equal to 100, I guess they could type in 0024. That gets really complicated. So this is only going to work with one o'clock on. For now, maybe we'll add it so we can, because typing in 0030 would be 30 minutes past midnight. That could be another one too. I guess we'd have to check the left two characters to see if those are zeros. Let's just do the civil version first.
So for now, it only works from times after one o'clock. Then this will be a time from 0100 hours to, I guess, 23 or 2400. Really, there is no 2400, it's 2359. Let's keep it simple. We'll tweak it later. I think it's best to get the general stuff done first.
We'll need two more values. Now we have to separate out the h and the L, or the h and the hours and the minutes. We'll need two more variables, h as long and n as long. Why n for minutes, you say? Well, that goes with the dateadd and the datediff formats. m is month, so n is minutes. I'm just staying consistent with Access. The h, the hours, is going to be L integer divide — not this guy, that's regular divide — we want integer division — 100. So 730 becomes 7. We're basically dividing it by 100 and ignoring any remainder. I cover integer division and modulus and all that in my Access Expert Level 26 class. It's one of the more advanced math functions.
So now I got the hours out of it. How do I get the minutes part of it? Well, n, minutes, is going to be L mod 100. So 730 becomes 30. We divide it by 100 and we just want the remainder portion.
OK, and now default food time text becomes h and a thingy and n. So 730 becomes 7:30. Now we do have an issue where if n is less than 10, we have to add a zero there. This might be better off — actually, let's do it like this. Let's say default food time text equals h and that. Let's cut this off. Here, we're going to say if n is less than 10, then default food time text equals default food time text and an extra zero. Then we'll say default food time text equals default food time text and the n there. So now we got seven and then the 30, and if we need that extra zero, it puts it in there. These are the little things you have to think of. Although most of the time, when people enter military time, it's either the top of the hour or 30 or 15 or whatever. I almost never see it like 07:02.
Let's end if there and if there and if we get to this point and this guy has been rewritten, it should flow through this then.
We still have a couple little tweaks to make, but let's just give it a test — real quick test, debug, compile. Let's come back out here. Now, let's... not finished. I like to get the big strokes done first.
Let's come in here. I'm just going to type in a 5 — boom, 5 AM. Nice. Let's type in a 20 — boom, 8 PM. That's perfect. How about 200? Boom, 2 AM. OK, good. How about 1500? 3 PM, good. How about 1745? 5:45 PM, that's great. OK, so it's working mostly. 2350, 11:50 PM. Beautiful.
Now we've got some edge cases. What if they type in a number that's too big, like 4000? See, it's going to not know what to do to handle that. Let's see, why didn't it make it 11:01 PM? See, it's going to just add and do kind of weird stuff, so we have to take that into consideration.
If L is greater than 100 and L is less than or equal to 2359, anything bigger than that's not going to work with this. Save it. If I type in — oh, that's why it went to 11:01, because it is now 11:01 PM. At 4000, it didn't know how to deal with that, so it put the current... I was trying to figure, why did it pick 11:01 PM? Because it is 11:01 PM.
Let's try a smaller number. Let's do 2000 — 8 PM. Let's do 3000 — see, 11:02, it can't handle that. How about negative 900? 11:02, good. It can't handle that either. Great.
We should probably say here, if L is greater than or equal to 0 and L is less than 24, let's do this too. Let's do L is less than 2400, that makes more sense.
I think what we're going to do now is — all right, so 0 through 24, it's going to assume the hour. If they type in 100 through 2400 — well, less than 2400 — it's going to assume military time.
I think the best way to do it after that is any other number less than 100, we can just assume it's the minutes, like if they typed in 0030. It can't be 30 hours, so we'll just assume that's 30 minutes. I think that's probably the best way to handle it.
Or you know what we could do — now see, this is what happens sometimes when you write version one of an algorithm and you think, you know what might be better here is to look at the number of characters that they typed in. If they typed in numbers and they only typed in one or two digits, then assume they want the simple hour instead of looking to see if it's less than 24. If they typed in three or four digits, they might have typed in 730, 930, military style without the leading zero. If they typed in all four digits, then we know they want military style. So we can approach it that way.
I don't want to totally start over, but let's start over. We're going to rewrite. I do this a lot. I kind of made some outlines of what I want to do today, but I always get better ideas as I'm going along. What we did was not invalid; there's just a better way coming up to do it.
So let's do this, and so I don't have to keep retyping default food time text. Let's put it in a string. Let's say S as a string, we're going to say S equals default food time text, which makes the code easier to read.
Start off by trimming it. Let's trim this, so in case there happens to be a leading space, we'll get rid of it right off the bat. Now, if IsNumeric(S), then we've got a number. If not, the rest of that stuff down below is just fine. We know at this point we have a number, so we can now put L equals CLNG(S). That's good. Let's now figure out how many digits they typed in.
We'll do a Select Case, the length of S. How many characters were typed in? End Select. If you've never done a Select Case statement before, I got a video on that — here it is, you'll find a link down below.
Case 1 or 2 means it's a simple hour, like 7 or 23, in which case it's pretty much the same thing we did before. Default food time text equals L & ":00" like that.
Case 3 or 4 means military or 24-hour time, like 700 or 1930. In this case, again, we have to split these up. So h equals L \\ 100 to get the hours, n equals L mod 100. Now, what we're going to say is default food time text equals h & ":" & Format(n, "00"). What that does with the format command is it says, I want you to take this n and display it in this format. So if it's just a 2, you'll get 02. If it's a 12, you'll get 12. That's a neat little formatting trick. Again, I got another video where I cover that. I don't remember where. I don't feel like looking that one up. The easy ones, like Select Case, I just type in "select case," there it goes. The format one, I don't think it's in my format video. I got a different video where I cover that.
But that should do it. If they type in anything other than that, then it's OK to drop out, and this will just handle it. So we don't need a Case Else inside here.
This just goes to show that sometimes after you write the code the first time to get it to work and you see what it looks like, then your brain says, you know what, we can simplify that and do it a different way. That's when I thought, hey, instead of just looking at the numeric value, let's look at the number of characters they type in.
I'm the same way with loops. Whenever I think of some complicated data sets, my brain always thinks, OK, we have to loop through the customers and do this for each one. Then after I write that and get it working, then I say, hey, let's see if we can rewrite this as an SQL statement, which nine times out of ten runs faster.
Anyways, debug compile once in a while.
Now let's give it a shot. Let's try 9 — 9 AM, good. How about 17? 5 PM. Good. How about 930? 9:30 AM. How about 1730? 5:30 PM. Beautiful. How about 5555? 11:15 PM, which we're now at 11:15 PM. How about 0023? 12:23 AM. Perfect. We caught that edge case. That's it. I think we covered everything. Like 0034 — boom, 12:34 AM. Perfect.
That is how you do the simple hours like I want or the military time. That should answer whoever emailed me — a couple people — since, like, week 13, asking, can we do the military? Sure. Sometimes by writing the code that you can easily think of as you're typing it in, those edge cases come to you — what if they do this? What if they do that? Then we have to rewrite it this way. Sometimes the code just evolves and even gets simpler as you're writing it. I've had whole pages of code that were like 50 lines that, after I sat there and looked at it and analyzed it a different way, or sometimes I slept on it — like it's 11:16 at night right now — I bet if I slept on this, I'd get an even smarter idea tomorrow morning. I'd realize, wait a minute, we can do it this way.
Members, we've got an extended cut today. I'm going to show you how to make an option with a checkbox to keep the time when you copy items from a previous day. This Copy Item button was something we added in an extended cut in the past. It gives us an option to copy items so I can go back to a day before. I can say, OK, I want to copy my fruit bowl to today. Copy, copy, copy. It copies all these items.
The problem is I don't always want to copy this time. Sometimes, let's say it's nine o'clock, I'm going to bed early. I want my snack now. I want to copy this, but I don't want it to say 11 PM. For whatever reason, most of the time, I do it with other stuff. If I'm having dinner earlier, whatever.
We're going to make a checkbox here, which you probably have seen throughout this video. It's been down there because I was messing around earlier, but we're going to make it work in the extended cut. So when you do this, if you turn this off, it will copy these items to today but use the current time instead of this time. That's going to be covered in today's extended cut.
Silver members get access to all of my extended cut videos. There are hundreds of them, so you have tons of stuff to watch. Gold members can download these databases and you get access to the code vault and a lot more. Every member gets some free training, like my full courses and lots and lots of stuff. So click that blue Join button today.
That's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Entering 24-hour time values without a colon in Access
Automatically converting typed numbers to Access time
Assuming AM for single or double-digit hours
Interpreting numbers like 15 as 3 PM
Handling 3 or 4 digit entries as military time
Splitting numeric values into hours and minutes
Using integer division and modulus to separate time components
Formatting minutes with leading zeros for display
Validating time entries and preventing errors with out-of-range numbers
Using the length of user input to determine time format
Using a Select Case structure to process user input length
Automatically formatting valid 24-hour time for Access fields
Debugging and testing various time input cases in Access
Cleaning and trimming text box input before conversion
Improving and simplifying input algorithm as needed
COMMERCIAL:
In today's video, we're learning about entering 24-hour and military time in Microsoft Access. You'll learn how to set up a text box so you can type times like 700 for 7 AM or 1500 for 3 PM, and even make it so typing just 9 will assume 9 AM. I'll show you how to handle numbers with and without colons, and explain the code changes needed to make this work smoothly, including how to handle edge cases and simplify your code as you go. Also, in today's Extended Cut, we'll add a checkbox to keep or update times when copying items from previous days. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary problem the video aims to solve in Microsoft Access regarding time entry?
A. Automatically calculating time differences between records
B. Allowing users to enter 24-hour (military) time without a colon and having it convert to an Access-friendly format
C. Restricting time entry to only AM values
D. Forcing all time entries to be in text format only
Q2. When a user types "700" in the text box, what should be the resulting time displayed?
A. 7:00 PM
B. 7:00 AM
C. 17:00
D. 12:00 PM
Q3. How does the revised code determine whether a user entered a simple hour (like "9") or a military time (like "1530")?
A. By checking if the value is greater than or less than 1200
B. By inspecting the number of digits entered
C. By looking for a colon in the input
D. By testing the value against a list of valid military times
Q4. Which method does the video recommend for converting a number like "730" into proper hours and minutes?
A. Dividing by 24 and taking the remainder
B. Integer division by 100 for hours and modulus 100 for minutes
C. Multiplying by 60 to get seconds
D. Adding 12 to convert to PM times
Q5. In the system described, what happens if a user types just a single or two-digit number, like "6" or "17"?
A. The system rejects the value as invalid
B. It treats the entry as minutes past midnight
C. It assumes the value refers to the hour, setting minutes to zero
D. It prompts for clarification if it is AM or PM
Q6. How does the revised code ensure that minutes are always displayed as two digits (e.g. "07:02" instead of "07:2")?
A. It uses the Format function to format the minutes value as "00"
B. It always adds a zero before single-digit numbers
C. It requires the user to enter a leading zero
D. It ignores minute values less than 10
Q7. What is the advantage of using Select Case over multiple If-Then statements in this context?
A. It only works with string values
B. It allows for easier handling based on the length of the entered value
C. It is faster than writing loops
D. It forces the user to input colons
Q8. What is the edge case that the final code revision handles more effectively compared to the initial attempt?
A. Values over 2400 are automatically converted to midnight
B. Values like "0030" (30 minutes after midnight) are recognized and properly converted
C. Negative time input is processed as 24 hour format
D. The code rejects any value without a colon
Q9. According to the video, why is it better to check the length of the input string rather than just its numeric value?
A. It allows the code to accept negative numbers
B. It can distinguish between hour-only and military time entries more intuitively
C. It helps Access handle non-numeric entries
D. It is necessary for AM/PM identification
Q10. What tip does the presenter share regarding evolving or improving code while working on projects like this?
A. Never change your code once it works
B. Expect to rewrite code for edge cases and to simplify logic as you understand the problem better
C. Always write the most complex version first
D. Avoid using functions like Format or Select Case
Q11. In the extended cut, what additional option does the presenter show regarding copying items to a new day?
A. Automatically copying the previous day's entire database
B. A checkbox to control whether the copied item's time is preserved or replaced with the current time
C. Forcing all copied items to be marked as completed
D. Blocking copying if the item was entered after 9 PM
Q12. Which of the following is NOT a use case the presenter mentions for this kind of time entry logic?
A. Fitness tracking database
B. Time tracking or work log database
C. Payroll processing for multiple employees
D. General databases that require time input simplicity
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-A; 7-B; 8-B; 9-B; 10-B; 11-B; 12-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone focuses on how to type 24-hour time, also known as Military Time, directly into a Microsoft Access database field and have it automatically convert to a standard time format that Access recognizes.
Normally, Access does not accept entries like 700 or 1530 as valid times, but with the technique I show today, typing 700 will be recognized as 7:00 AM. The method even allows you to enter just a simple hour value like 9, and it will be interpreted as 9:00 AM, without needing to specify AM or PM. If you type 15, the system will understand it as 3:00 PM. This approach not only supports Military Time but can also make time entry easier, since you can just type something like 6 and Access will treat it as 6:00 AM.
Even if you have seen my previous video on Military Time, there is a new trick in this lesson that you may find useful. The specific techniques I'll show are part of my Fitness Database series, episode 58. That said, you do not need to be interested in fitness or have watched any earlier videos to benefit from this lesson. The skills covered here are helpful for any context where you handle a lot of time entry, such as time tracking, work logs, or any application where users may prefer entering time in a quick numeric format.
A common request I get from students is to allow entering 24-hour time without requiring the colon, so inputting something like 2300 should convert to 11:00 PM automatically. The default Access behavior, if it cannot interpret the input as a valid date or time, is to revert to the current date and time. My goal is to let users type 2300 and get 11:00 PM, or simply 9 and get 9:00 AM.
I show how to implement this with the default time entry field, which is generally the only one most users interact with regularly. The process begins by examining the After Update event for this field, which is currently set to use the current time if the input is not a valid time value.
To enhance this, we first determine whether the user entered a number. If so, we process it as either a simple hour or a military-style time, depending on its format. For single and double-digit numbers like 7 or 23, these will automatically be converted to 7:00 AM or 11:00 PM. If the value is a three- or four-digit number (such as 730 or 1530), it is parsed as military time with hours and minutes separated accordingly.
The technique involves determining the number of digits in the user's input. If an entry contains just one or two digits, we treat this as an hour and add ":00" for the minutes. For three- or four-digit entries, we split the value into hours and minutes by dividing and taking the remainder using integer division and modulus functions. To maintain proper formatting, if the minutes value is less than ten, a leading zero is added.
Along the way, I discuss some edge cases and how the code needs to be adjusted to avoid issues with impossible values like 4000 or negative numbers. If the input is out of a reasonable range for time, the system falls back to its default behavior.
I also emphasize that sometimes, as you write code, you find ways to refine and simplify your approach. In this case, after starting with a value-based logic, I switched to examining the length of the input string which makes the code neater and handles edge cases more elegantly.
This lesson includes explanations about why certain conventions are used in Access, such as using "n" for minutes in line with the way Access handles date and time functions, and how integer division and modulus work.
Testing demonstrates that you can now enter any reasonable 24-hour or simple hour time, such as 9, 17, 930, 1730, or even 0023, and the input will correctly convert to an Access-friendly time value.
For those who are members, today's Extended Cut includes a useful feature: adding a checkbox that lets you choose whether to keep the original time when copying items from a previous day. For example, if you want to reuse a record's contents but enter the current time instead of the copied time, toggling this checkbox will handle that. This is a practical enhancement shown in detail in the Extended Cut video.
Silver members have access to all extended cut videos, with hundreds available for even more advanced Access techniques. Gold members can also download the sample databases, access the Code Vault, and receive additional member benefits. All members gain some free, comprehensive training as well.
You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Entering 24-hour time values without a colon in Access
Automatically converting typed numbers to Access time
Assuming AM for single or double-digit hours
Interpreting numbers like 15 as 3 PM
Handling 3 or 4 digit entries as military time
Splitting numeric values into hours and minutes
Using integer division and modulus to separate time components
Formatting minutes with leading zeros for display
Validating time entries and preventing errors with out-of-range numbers
Using the length of user input to determine time format
Using a Select Case structure to process user input length
Automatically formatting valid 24-hour time for Access fields
Debugging and testing various time input cases in Access
Cleaning and trimming text box input before conversion
Improving and simplifying input algorithm as needed
Article
If you want to make it easy for users to enter times into a Microsoft Access database using the 24-hour format (known as Military Time), or even simpler, just by entering a single or double-digit hour, you can create a system that automatically interprets these inputs into valid time values for Access.
Normally, Access expects time values in a format like 8:00 or 15:30. If you just type in 900 or 1730, Access may not interpret these correctly. Our goal is to let users type in things such as '700' for 7:00 AM, '15' for 3:00 PM, or simply '9' for 9:00 AM, and have the code convert those numbers to times Access can handle.
To accomplish this, we use some VBA code in the AfterUpdate event of your time entry text box (for example, the default food time text box in a fitness log). The code checks the input as soon as the user finishes entry and clicks away (or hits enter). If the input is numeric, it will convert the entry into an appropriate time format.
Let me walk you through how to do this and explain the reasoning for each part so you can adapt it to your own database.
First, we make sure we capture the user's input, trim any extra spaces, and check if it's a number. If it is, the next step is to figure out if they typed in a single or double digit (like '7' or '23') meaning 7:00 AM or 11:00 PM, or if they used three or four digits (like '700' or '1930') for Military Time.
To process this, we'll use a Select Case statement based on the number of digits entered. For 1 or 2 digits, we'll treat it as the hour. For 3 or 4 digits, we'll split it into hours and minutes according to 24-hour time conventions.
Here is the VBA code you would use in your text box's AfterUpdate event:
Private Sub DefaultFoodTimeText_AfterUpdate()
Dim s As String
Dim l As Long
Dim h As Long
Dim n As Long
' Get the user input and trim spaces
s = Trim(Me.DefaultFoodTimeText)
' Check if the input is entirely numeric
If IsNumeric(s) Then
l = CLng(s)
Select Case Len(s)
Case 1, 2
' One or two digits: treat as hour with zero minutes
Me.DefaultFoodTimeText = l & ":00"
Case 3, 4
' Three or four digits: split into hours and minutes
h = l \\ 100
n = l Mod 100
' Format minutes as two digits (e.g., 7:02, not 7:2)
Me.DefaultFoodTimeText = h & ":" & Format(n, "00")
End Select
End If
' You can now continue with any additional processing as needed.
End Sub
Let's break it down further. The code first grabs the input as a string and removes any leading or trailing spaces. If the input is all numbers, it converts it to a long integer for processing. Next, it checks how many digits the input has:
- If the input has one or two digits, like '9' or '17', we interpret this as an hour and append ':00' to default to the top of the hour (so '9' becomes '9:00', '17' becomes '17:00').
- If the input has three or four digits, like '900', '1730', or '0030', we interpret the left part as hours and the right part as minutes. To get these, integer divide by 100 for hours, and use modulo 100 for minutes. The format function ensures minutes are always two digits, so if someone types 701, it becomes '7:01', not '7:1'.
This method covers most typical time entry scenarios—whether users want to enter simple hours, full 24-hour times, or even leading zeros.
You can further extend the code to handle inputs like '0034' (which becomes 12:34 AM), handle invalid entries (such as '4000'), or prevent negative or otherwise illogical values as needed.
This approach saves significant time for users, letting them quickly type times without worrying about colons or AM/PM, and is useful not just for fitness tracking, but any Access database where rapid time entry is important, such as work logs or scheduling.
If you want to adapt this to other text boxes or forms, simply copy this code to the AfterUpdate event of the relevant text box and change 'DefaultFoodTimeText' to your control's name.
By building this kind of user-friendly logic into your Access applications, you make data entry much faster and clearer for everyone who uses your forms.
|
