Table calculated field creation?

[BigDogTroy]

Is it possible (and logical) for me to create a table with a calculated field from 2 other fields in the same table? Example; I have a long time field (mm/dd/yyyy hh:mm:ss) aka StartTime, and a long time field EndTime. I would like to subtract the StartTime field from the EndTime field to get the total time elapsed, but in hh:mm:ss format. I can get it in hh:mm format, but I really need the hh:mm:ss format. Any Ideas?

 

[DJkarl]

It's bad practice to store calculated data, create a query that does this calculation and use it instead.

 

[neileg]

I agree with DJkarl. I would add that it's preferable to apply the formatting in your form or report, not at the table level.

 

[gemma-the-husky]

having said that when you subtract two dates, you will get a date - now how you display that date is up to you - if you are only seeing hrs mins, then perhaps your date/time format is wrong - perhaps you are using short time, and a different format is appropriate

bear in mind with times, that access wont EVER go ober 24hrs, and time a - time b mmay not give you the expected result - its more sort of modulo 24hrs (but that may not be technically exactly right)

 

[The_Doc_Man]

I'm going to quibble, Gemma. The difference between two dates isn't a date, it is a date FIELD - but it is actually an INTERVAL and that leads to issues in the way that Access handles date fields. See, Access doesn't know intervals, it only knows dates. Just because the field SAYS it's a date, it could lie. Happens a lot with Access.

This happens because DATE fields are actually what is called a CAST or TYPECAST. A DATE field is a DOUBLE that equals the elapsed time in days and fractions thereof from the reference date/time - midnight of 31-Dec-1899 = day 0. 1-Jan-1900 is day 1.

When you take the difference between two dates, you get a difference in days and fractions thereof. To get this in a decent format, you might have to multiply the difference by 24 to change days to hours, thne you can show hours and fractions. Formatting this to hhhh:mm:ss format is best done with a function. Search this forum for "time conversion" or "interval conversion" threads.