|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Time as a Duration By Richard Rost 
6 years ago
Display Time as a Duration in Hours / Minutes Microsoft Access doesn't have a native time format that you can use to enter a duration such as 105 hours and 23 minutes. This is especially useful in some professions, like airplane mechanics who have to note how many "flight hours" a plane has on it. In this TechHelp video, I'll show you how to handle it. We will learn how to perform the calculations to determine total hours and/or minutes, the Round function, and some string manipulation functions like Left, Right, and InStr to deal with the non-standard time format. MembersThere is no Extended Cut for this video. LinksYou can download the database I built here: Access Duration Times ZIP
IntroIn this video, we will talk about how to handle storing and working with time values as durations that can exceed 24 hours in Microsoft Access, such as flight hours and minutes. I will discuss why traditional date/time fields are not suitable for this purpose, and show you two methods: using separate fields for hours and minutes, and storing durations as text in the HH:NN format, then converting them for calculations. We will also look at validation rules, formatting, and how to perform basic calculations on these values in queries.TranscriptWelcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor, Richard Rost.In today's lesson, we're going to talk about entering times into your database that are durations in hours and minutes, and they may exceed 24 hours. These are not valid date time field objects. You can't put a value in your Access database that is 104 hours and 16 minutes, for example. Let's take a look at the question. This question comes from Jeff. He says, I am an aircraft mechanic and I have to cope with bunches of Excel forms to make records of aircraft daily flights, monitor conditions of each critical part, arrange the upcoming inspections on aircraft, report historical records of flights, and so on. All I've been used to is just Excel and Word, but the files are so many and it is hard to manage them in an orderly and safe way. Of course, that's one of the reasons why I want to use Access because those Excel files can definitely add up, or sheets in one Excel file. Then last week I watched your Access video. It was awesome. Thank you very much. An Access database can definitely make my work more efficient. Now I'm thinking of building an Access database for my daily job. First up, I have to construct tables. For example, flight hours or aircraft total time should be input into the field, but I found that hours:minutes fixtures filling there cannot be input, for there is no appropriate type setting for this field. Number and datetime cannot do this job. Short text can make the input, but cannot be calculated. And that's another problem. What should I do for these values? How to make the flight time such as hours:minutes be input into the fundamental tables? Your advice is really appreciated. Thanks for your time. Best regards, Jeff. You have hit the nail on the head there, Jeff. Right there, 3456 hours and 50 minutes is not valid for a datetime field. You can't put a number in like that. You could put it in as a number of hours and then a fraction of minutes, but then you have to do a division in your head, for example. You can enter a short text value such as 6 hours, 28 minutes, but again, if you get over 24 hours, you're going to get an error. The problem is that Access stores dates and times internally as a number, and I go over exactly how that number is calculated in my Access beginner classes. Essentially, there is no data type that will calculate hours and minutes like this as a duration of time that could exceed 24 hours. Then you start getting into it becoming days. How do you handle this if that's how you want to input your data? Essentially, there are two ways to handle this situation. One is to use a separate field for hours and minutes. This is my recommended method. If you can retrain yourself and your staff to enter hours and then hit tab and then minutes, I recommend using two fields. Let's take a look at method one here. Let's design a table. ID, auto number. Let's call it flight hours. That will be a number of type long integer. Then flight minutes. That will also be a number of type long integer. Save it. We will call this my flight one table. Primary key, sure. Then let's put some data in here. Flight hours, 67 hours, 13 minutes. If you can do that for your data entry, then this is the easiest solution. Flight hours, 8, 3 minutes. You can format this to show two digits here if you want. That's an easy format. Just go into the design view, go into flight minutes here and for format, put in 00. You do not necessarily want to do that for hours because you might get 6 hours and you just want to see the 6. For minutes, it's always going to be like that. Also for minutes, while we're in here, you can also say that minutes has to have a validation rule where it has to be between 0 and 60. You do not want someone typing in 64 minutes. That should be another hour. You can control that here. You have a lot more control if you do it this way. I saved that and got a warning about the data integrity, it might have changed because of the validation rule, but nothing in the table so far violates that rule so we're good. Now if I try to come in here and put in 120 hours and 90 minutes, I get yelled at, it says between 0 and 60. You can put your own custom validation message in there if you want to. Now we've got some sample data in here. To do calculations on this data now is very simple. If you want to calculate the total minutes or the total hours, you can easily do that in the query. Create query design, bring in your table. Bring in all the fields if you want and you can do this in a form too, you do not have to use a query, although I do recommend using a query. You can say my total minutes is going to be equal to flight hours times 60 plus the flight minutes. Now I have total minutes over here and you can use that for your calculations. If you want to represent that as hours instead of minutes, you can say total hours is going to be total minutes divided by 60. That is your total hours. If you want to round that number, you can certainly round it, round that big number, comma, two decimal places. There you go. There is your total hours. If you are happy entering in that information, this is my recommendation. This is how I would suggest to do it. Now, what if your people are stubborn and they do not want to get away from the HH:NN format, or you have a bunch of data that you have to import from Excel for your legacy records and you want to convert that over to this? How do you go about doing that? That is condition number two. Let's save this real quick, flight one Q. Let's make another table and I will show you the other way to do it. Create table design. Sorry, I am getting used to a new mouse so my hand might seem a little jumpy. Again, an ID. Now this time the flight time is going to be entered in as short text. We have to put it in as text, can't put it in as ID time. Can't put it in as number because we're putting a colon in the middle of it. Save this, flight two T. Primary key defined there. Now let's put our flight times in here: 1 hour, 18 minutes. You do not have as much control here. You cannot easily prevent someone from typing in 1 hour, 65 minutes, so that is why I recommend the other way. 135 hours, 7 minutes. That will be valid because once we do our trick to get them to pull the minutes out, it will still work. 69 hours and no minutes. 115 hours and 13 minutes. Now we have to split this apart using text functions. Then we can convert those text values into numbers and we have to do that in a query. Again, you can do it straight in a form. I recommend a query. Create query design, pull in flight two T. The first thing we have to do is figure out where in that string the colon is, that's what delineates between the hours and the minutes. I am going to call it colon position, colpos: Where is it? Use the InStr function in flight time, comma, look for the colon. I know sometimes people complain when I do not zoom in so they can see that better. My video recording software does not easily allow me to zoom in and out without changing the dimensions of the window and stuff like that. I need better software, but I have been using this software since it was HyperCam. I have been using it since I think 2004 and it is really good software. I love it, but it's not easy to zoom in and out. So I am going to just pop it up on notepad for you so you can see it. I am doing colpos is the InStr of flight time, comma, that little colon there. If I run that now, you can see I get a number that represents the position inside that string where that colon is located. I cover a lot of these functions in other videos. InStr, you are going to see Left and Right in a minute. I have tons of other videos that cover this stuff, including my full classes. Now we know where the colon is. Now I can use the Left and Right functions to pull out the hours and minutes. Flight hours is going to be the Left of flight time, comma, where is that colon? So colpos. Then I have to subtract one because I do not want the colon itself. Run it now. There we go. There are the flight hours. It finds the colon and takes the left x number of characters. Here is four or that colon is in position four right there. So give me the left three characters. That is how that works. Then, so I can do calculations, I want to convert this over to a long integer, CLng, convert to long, that whole thing. Now it is a number. You can tell because now it lines up on the right side of the field there. This is a number I can now add and subtract and multiply and do calculations with. Let's get the minutes. Minutes are a little harder because I have to come in from the right side. Flight minutes is going to be the Right of flight time, comma, the Len of flight time minus the colon position. In other words, take the right number of characters; how many? Take the total length of the string and subtract the colon position. So if the whole thing is 10 and the colon is 3, I want seven characters. Again, we take this whole thing and CLng it so we can now do math on it. There we go. Now I have flight hours and flight minutes in two separate fields. Again, this value here, if someone types in a value without a colon, you will get errors here. It is up to you which way you want to do it. Of course, you can put all kinds of controls on this and if you know VBA, you can review this value after it is entered to make sure it is valid. Now you can use the other techniques I showed you to get the total minutes and the total hours if you want, using these two values. I will save this as my flight two Q. There we go. Just so you have it, you have a nice view of the different functions there: flight hours and flight minutes. That is how you do it. I cover all of these functions in my complete lessons, and I have got some other tip videos on my website and on YouTube about how to use these things. I will put links in the description below the video to take you to different videos that teach you things like CLng and Left and Right and all that. We can actually just go into flight one and grab these guys: total minutes and total hours. Copy that, slide over here, paste these in. Now total minutes will be flight hours times 60 plus flight minutes. Those functions should work just as is. There we go. Perfect. Just copy them right off the other one. Total minutes, total hours. Here I will put those in the notepad for you to see, you can get a copy of those. Copy that, paste it over here. Let us grab hours. I will paste that in there for you so you can see what they are. There you go. Pause the video because I am moving on. I will drop a copy of this video on my website or of this database on my website too. I will put a link at the bottom. You can come and grab it if you want. Thanks for watching. I hope you learned something. If you are watching me on YouTube, make sure you subscribe to my channel and ring the bell so you get notifications when I release new videos. If you are watching on my website, make sure you subscribe to my Microsoft Access Forum and you will also get notifications when I post new videos. Want to see your question answered? Send it to me. The best way is on my TechHelp page. Now it is time for some shameless advertising. If you liked this video, make sure you watch my free Access Level 1 class. There it is right there. It is both on YouTube and on my website and that is the one that Jeff was referring to in his question earlier. It is a full three hour long tutorial. It teaches you all the basics of Access and even if you have been using Access for a little while, there is a lot of good fundamental information in there that even some veteran users of Access do not know. If you like Level 1, you can get Level 2 for just one dollar and there is the link. I hope you learned something. Thanks, and we will see you next time. QuizQ1. Why can't you use the standard Access Date/Time field to store flight times like 104 hours and 16 minutes?A. Because Date/Time fields in Access do not support durations greater than 24 hours B. Because Date/Time fields require input in seconds only C. Because Access Date/Time fields are not compatible with numbers D. Because fields must be in short text format to input time Q2. What is the recommended way to store durations in hours and minutes that can exceed 24 hours in Access? A. Store time as a single short text field B. Use two separate number fields: one for hours and one for minutes C. Use a Date/Time field and format it to allow for more than 24 hours D. Use an OLE Object field for hours and minutes Q3. If you use two separate fields for hours and minutes, what data type should you use for these fields? A. Date/Time B. Short Text C. Long Integer Number D. Currency Q4. Why should validation be added to the minutes field when using number fields for hours and minutes? A. To prevent entry of negative numbers B. To prevent minutes greater than 60, which should be counted as another hour C. To prevent entry of numbers less than 5 D. To prevent minutes equal to zero Q5. When storing time duration as two fields, how can you calculate the total minutes using Access queries? A. Add hours and minutes fields together B. Multiply hours by 60 and add the minutes C. Add hours multiplied by minutes D. Divide hours by 60 and add minutes Q6. In method two, where the time is stored as a text field (e.g., "67:13"), which function is used to locate the position of the colon in Access? A. Left B. CLng C. InStr D. Mid Q7. To extract the number of hours from a text field containing "HH:NN", which function combination is recommended? A. Use Mid to extract hours B. Use Left to get characters before the colon, then convert to Long Integer (CLng) C. Use Right to get hours, then CLng D. Use Len to determine hours Q8. What's the main disadvantage of storing flight time as "HH:NN" in a single text field, as opposed to using two number fields? A. More storage space is needed B. You cannot easily perform data validation or calculations without extra steps C. It cannot be displayed in forms D. Users cannot enter any values Q9. When extracting minutes from the "HH:NN" short text field, which combination of functions should you use? A. Right and CLng, using Len minus the colon position B. Left and CLng C. InStr only D. DateDiff Q10. If you import legacy data in the "HH:NN" format and want to convert it to separate hours and minutes for calculations, what tool is recommended? A. VBA module only B. Query with text functions C. Table macros D. Data macros Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-C; 7-B; 8-B; 9-A; 10-B 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. SummaryToday's video from Access Learning Zone focuses on how to enter and manage time durations in hours and minutes within your Access database when those durations might exceed 24 hours. This is a common issue for those of us who need to log time that goes beyond a single day, as standard DateTime fields in Access do not support values such as 104 hours and 16 minutes.The inspiration for this lesson came from a question by an aircraft mechanic who handles a lot of Excel files to track flight records, aircraft inspections, and historical flight data. He recently decided to transition to Access to better organize and safeguard his data, but quickly discovered that Access does not offer a field type that seamlessly accepts hours and minutes in a format exceeding 24 hours. While short text fields do allow the entry of formatted values like "6:28", these fields do not provide the ability to perform calculations, making them less than ideal for tracking and reporting purposes. Access stores dates and times as numbers internally, but it does not have a built-in way to treat extended hour:minute durations as simple numeric values or durations that can be calculated automatically. If you try to use a DateTime field, it will not permit values beyond 24 hours, and a single numeric or text field brings its own limitations. There are two main methods you can use to work around this problem. My recommended approach is to use two separate fields: one for hours and one for minutes. This method requires users to enter hours in one field and minutes in the next, which does require adjusting your data entry habits, but it gives you far more control and flexibility during data entry and reporting. You would create a table with an auto number ID field, a field for flight hours (as a long integer), and another field for flight minutes (also a long integer). You can set the minutes field with a validation rule to restrict values between 0 and 60, preventing errors like entering 64 minutes instead of 1 hour and 4 minutes. You can also format the minutes field to always display two digits for a consistent appearance. With this setup, calculations are straightforward. If you want the total number of minutes, simply multiply the hours by 60 and add the minutes. To get the total hours as a decimal, divide the total minutes by 60. You can easily round this value if needed. This makes reporting and data analysis much more reliable. However, sometimes you may need to work with legacy data from Excel or deal with users who insist on entering time in the traditional "HH:MM" format. In these cases, you can use a text field to store the time string, then use queries to extract the hour and minute values. This involves using text manipulation functions like InStr to locate the colon separator, Left and Right to pull out the hour and minute portions, and CLng to convert those portions into numbers for calculations. While this method works, it is more error-prone, and you lose the validation and formatting benefits of using separate fields. Additionally, if someone enters a time value without a colon, your queries may struggle to process those records correctly unless you implement further checks. After splitting the text field into hour and minute components through a query, you can apply the same methods described earlier to calculate total minutes and total hours, making these values usable in reports and further calculations. Throughout this lesson, I demonstrate how to create these fields, apply formatting and validation, and write queries for extracting numeric values from text fields. I also discuss the relevant Access functions used for these operations, like InStr for locating characters in a string, and Left and Right for extracting portions of a text field. If you want a hands-on example and all of the step-by-step instructions for everything we covered here, I have a complete video tutorial available on my website at the link below. Live long and prosper, my friends. Topic ListStoring durations over 24 hours in AccessCreating separate fields for hours and minutes Formatting the minutes field with leading zeros Setting a validation rule for the minutes field Calculating total minutes from hours and minutes Calculating total hours with decimal values Importing HH:MM formatted text data Parsing hours and minutes from text using InStr Extracting hours using the Left function and CLng Extracting minutes using Right, Len, and InStr Converting parsed text values to numbers for calculation Calculating totals from parsed hours and minutes fields |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access time duration HHNN flight time PermaLink Time as a Duration in Microsoft Access |
