My Assistant
Custom Search

Short Time Calculation, Access 2003 
Nov 27 2017, 10:41 AM Post#1  
Posts: 277 Joined: 5November 07 From: Wisconsin  Hello, I am trying to do a calculation in a query on the following and can not get it displayed properly: AmountOfEmployees1 (Number) = 2 TimeOfEmployees1 (Short Time) = 15:28 (hh:mm) This should result in 30:56 but my query result is 30.93. My expression is as follows: [AmountOfEmployees1]*[TimeOfEmployees1]*24. Thank you in advance and any help would greatly be appreciated. 
Nov 27 2017, 10:49 AM Post#2  
UA Admin Posts: 33,786 Joined: 20June 02 From: Newcastle, WA  Without seeing the actual values in the underlying tables, it's hard to guess, but here's one possibility. Doing math on "dates" is always risky, because there are actually two different ways we can talk about it. First, there are "point in time" values, such as "2017/11/27" which is the current date, and "07:46AM", which is the current time on my computer's OS. It can be formatted as a "Short Time" because it is a Point in Time value. However, there are also "elapsed time" or "duration" values, such as your 15:28, which is NOT a short time, but a count of hours and minutes that have elapsed. The problem can be that you need to use ONLY elapsed times, or durations here, not "Short Times". Also, depending on the underlying values themselves, you may be encountering rounding errors. So, let's step back and look at what is involved at the table level. Thanks. 
Nov 27 2017, 11:01 AM Post#3  
UtterAccess VIP Posts: 10,171 Joined: 10February 04 From: South Charleston, WV  Please explain how you get 30:56 from 2 X 15:28 X 24. 
Nov 27 2017, 11:14 AM Post#4  
Posts: 277 Joined: 5November 07 From: Wisconsin  projecttoday: The time should show 30:56 (hh:mm) whereas 15:28 (hh:mm) times 2 employees should result in 30:56 (hh:mm). If I do not multiply by 24 it results in 1.288 GroverParkGeorge: Below is my table definition Field Name Data Type AmountOfEmployees1 Number (Double) TimeOfEmployees1 Date/Time (Short Time) 
Nov 27 2017, 12:26 PM Post#5  
UtterAccess VIP Posts: 10,171 Joined: 10February 04 From: South Charleston, WV  I'm still more confused. You can't multiply a date/time field. You will have to use numerical fields instead. 
Nov 27 2017, 12:28 PM Post#6  
UA Admin Posts: 33,786 Joined: 20June 02 From: Newcastle, WA  Yup, that's what I said. You are not working with times here, you are working with Durations. Both can be formatted to LOOK like the same thing, but they are not. Store start and stop times as times. Convert the elapsed times between those start and stop times to minutes. Then you can do the math on those minutes. 
Nov 27 2017, 03:42 PM Post#7  
Remembered Posts: 18,324 Joined: 29March 05 From: Wisconsin  jdfellows, Pardon me for jumping in... can you explain your table design a bit? > AmountOfEmployees1 Number (Double) This seems to be a count of how many people performed some sort of task. The Double datatype is meant for gigantic numbers or extremely tiny fractions, using a floating decimal point. If the values in this field are always whole numbers and are always less than 32,000 you should use the Integer datatype instead. > TimeOfEmployees1 Date/Time (Short Time) This appears to be a length of time. Your calculated field seems to be trying to figure out the total number of "man hours" used up for a certain task. You may want to consider storing this as an Integer as well, specifically the number of minutes. Your data entry form can still allow the user to enter a twodigit number, a colon, and another twodigit number, but you would use VBA to convert this string into a number of minutes for storage. This way you can easily multiply the number of people by the number of minutes to get the total minutes. Divide this by 60 to get the number of hours, and the remainder is the number of minutes. Do you want to display 40 hours and 30 minutes as 40:30 or as 8 Days and 30 minutes? Finally, the presence of the number "1" at the end of each field name kind of implies that there are other similarlynamed fields in your table like "AmountOfEmployees2". If this is the case, you should stop what you're doing and Normalize your data. This sort of table design is known as a Repeating Group and more often than not causes more problems than it solves. For example, if you do have a field named AmountOfEmployees2 and it's some sort of "second shift for the same task" sort of thing, you should have a separate table for the count of employees and the total time worked. Hope this helps, Dennis 
Custom Search

Search Top LoFi  21st October 2018  11:34 AM 