I'm a newbie at this but I'm willing to learn the program. My problem is that I created a table with 14 fields(days) where time is accumulated daily. I then designed a query and tried to get the query to calculate a running total as the times are entered daily in a total field. Well I continuously get an error in the calculated field. I tried to create from the same table a form with the same fields and adding a text box with what I thought would be the correct formula. I failed again, but not giving up. I need some help in making this work for me. thanks.
Calculated Field with Time
- Thread starter coliver
- Start date
- Local time
- Yesterday, 23:39
- Joined
- Feb 28, 2001
- Messages
- 30,066
You must understand date/time fields in order to understand what is going on. They are not always obvious to work with.
The main question is, what is the data type in the table of the field where you are accumulating times? A secondary question is what method you use to derive the times you are adding up: Comparing two times or direct entry of a number of hours and minutes?
If the daily time entry is a date field, you can take the difference of two date/time strings and it will work fine to compute the elapsed hours. Your problem occurs when you add the times together. The result is also a date field, but in your case it is not a STANDARD interpretation of date fields, and that is where you run into the problem.
If it is a text field, you run into a problem of adding the times in the first place because you really can't add text fields. So there, you have two or more problems.
Here is how date fields work: Each date/time field is a DOUBLE (floating) number representing the number of days and fractions of a day since the Windows reference date, which is Midnight of 1-Jan-1900. For Windows, days BEGIN at midnight (time 00:00:00 military format) and END one clock tick before midnight (time 23:59:59.99999.... military format to extended precision.)
OK, let's look at this closer. Contemporary times are now about 38K (+ small change) days downwind of the reference. The difference between two such date/time fields is the exact elapsed time between them in units of days and fractions of a day. So if you use a "Time-OUT - Time-IN" method, the hours are correct. DOUBLEs are capable of holding time fractions accurate to about the microsecond for this range of times, so that should be more than enough accuracy for your purposes. Like, WAY more than enough.
BUT - when you add up these differences, the time you would output cannot exceed 23:59:59+. If it does, Access (and Windows) interprets that Date field as something on 2-Jan-1900. Let's say the total SHOULD have been 40 hours for the week. Forty hours is going to look like one day and 16 hours, or about 4 PM of 2-Jan-1900. Look familiar?
SO, the question becomes, how do you deal with this?
Easy! DON'T treat the total as a date field. Copy/convert it to a DOUBLE and multiply it by 24 (perhaps in a query). That's your time in hours and fractions of an hour, directly usable as a number of hours for your time accounting or whatever you are doing with it.
NOW, if you were using TEXT entry, your problem is to convert that text, which is probably in hh:mm format, to a time OR a number of hours. Once you have it in that format, you have the problem licked.
Hope this helps.
The main question is, what is the data type in the table of the field where you are accumulating times? A secondary question is what method you use to derive the times you are adding up: Comparing two times or direct entry of a number of hours and minutes?
If the daily time entry is a date field, you can take the difference of two date/time strings and it will work fine to compute the elapsed hours. Your problem occurs when you add the times together. The result is also a date field, but in your case it is not a STANDARD interpretation of date fields, and that is where you run into the problem.
If it is a text field, you run into a problem of adding the times in the first place because you really can't add text fields. So there, you have two or more problems.
Here is how date fields work: Each date/time field is a DOUBLE (floating) number representing the number of days and fractions of a day since the Windows reference date, which is Midnight of 1-Jan-1900. For Windows, days BEGIN at midnight (time 00:00:00 military format) and END one clock tick before midnight (time 23:59:59.99999.... military format to extended precision.)
OK, let's look at this closer. Contemporary times are now about 38K (+ small change) days downwind of the reference. The difference between two such date/time fields is the exact elapsed time between them in units of days and fractions of a day. So if you use a "Time-OUT - Time-IN" method, the hours are correct. DOUBLEs are capable of holding time fractions accurate to about the microsecond for this range of times, so that should be more than enough accuracy for your purposes. Like, WAY more than enough.
BUT - when you add up these differences, the time you would output cannot exceed 23:59:59+. If it does, Access (and Windows) interprets that Date field as something on 2-Jan-1900. Let's say the total SHOULD have been 40 hours for the week. Forty hours is going to look like one day and 16 hours, or about 4 PM of 2-Jan-1900. Look familiar?
SO, the question becomes, how do you deal with this?
Easy! DON'T treat the total as a date field. Copy/convert it to a DOUBLE and multiply it by 24 (perhaps in a query). That's your time in hours and fractions of an hour, directly usable as a number of hours for your time accounting or whatever you are doing with it.
NOW, if you were using TEXT entry, your problem is to convert that text, which is probably in hh:mm format, to a time OR a number of hours. Once you have it in that format, you have the problem licked.
Hope this helps.
Numbers changing in regular fields
I've been able to get the calculated field to work relating to time, but I need to use time in increments of .25, .5,.75 and whole hour increments (1.00). I input the numbers but they always round up or down to a whole number which will not give me an accurate total. I've changed my data type to numbers and I've tried to use decimal to get my results in the table which will show up in a query and I will eventually move to a form. How do I retain my input of numbers in increments in my table to get the results I'm looking for?
Thanks for the help.
I've been able to get the calculated field to work relating to time, but I need to use time in increments of .25, .5,.75 and whole hour increments (1.00). I input the numbers but they always round up or down to a whole number which will not give me an accurate total. I've changed my data type to numbers and I've tried to use decimal to get my results in the table which will show up in a query and I will eventually move to a form. How do I retain my input of numbers in increments in my table to get the results I'm looking for?
Thanks for the help.
- Local time
- Yesterday, 23:39
- Joined
- Feb 28, 2001
- Messages
- 30,066
For this one, you need to watch your data types very carefully.
If you are accumulating integer hours, forget it. You'll never get fractions.
However, it is possible to do this if Hours is, say, DOUBLE format:
Hours = CDbl( CLng( Hours * 4 ) )/ 4.0
This gives you Hours to the quarter-hour.
If you are accumulating integer hours, forget it. You'll never get fractions.
However, it is possible to do this if Hours is, say, DOUBLE format:
Hours = CDbl( CLng( Hours * 4 ) )/ 4.0
This gives you Hours to the quarter-hour.