Then how about this? (1 Viewer)

[QMDirk]

If I use: =Format([Time Down]-1-[Time Restart],"Short Time") to show the total minutes that have passed, even though it displays the value in terms of hh:mm, how do I Sum the field to show the total of all entries? When I used: =DateDiff("n",[Time Restart],[Time Down])*-1 I just surrounded it with Sum() like this: =Sum(DateDiff("n",[Time Restart],[Time Down])*-1).

Any suggestions?

 

[CJ_London]

the format function results in a string. DateTime is a decimal number where the value before the dp is the number of days since 31/12/1899 and the bit after represents the time since midnight expressed as number of seconds divided by the number of seconds in a day. ergo the maximum number of hh:mm that can be formatted as hh:mm is 23:59.

So your second method would appear to be the way to go, so what suggestions are you looking for? provide some example data to illustrate the requirement

 

[theDBguy]

I think it was already mentioned in your other thread that if you're calculating a time span that exceeds 24 hours or involve multiple days, you should not use the Format() function. Formatting a value is for display purposes. So, if you are going to do any math on that value, you shouldn't format it, or you should perform the math on the unformatted value.

So, in order for your DateDiff() expression to work, you must have the date values with the time. Is that what you have?

 

[QMDirk]

I think it was already mentioned in your other thread that if you're calculating a time span that exceeds 24 hours or involve multiple days, you should not use the Format() function. Formatting a value is for display purposes. So, if you are going to do any math on that value, you shouldn't format it, or you should perform the math on the unformatted value.

So, in order for your DateDiff() expression to work, you must have the date values with the time. Is that what you have?

If by default, Access stores the date along with the time, in a field formatted as Short Time, then yes. Otherwise, I've done nothing to deliberately cause the date to be stored with the time.

also, I realized earlier that when I tested this, I just typed in the 2 time values. Would it make a difference if the values were entered in real time? I mean, would Access store tomorrow's date with the time for a time entered after midnight? I haven't tested that one yet. But as I said, the 2nd example works, it just gives the answer in an unwanted format. I can learn to live with it but I can't seem to show the total now.

What I have is 2 fields: [Time Down] and [Time Restart]. In the footer is an unbound text box named [Text24]. Previously, I used [Text24] to display the total downtime with: =Sum(DateDiff("n",[Time Restart],[Time Down])*-1). But the code for the Format()... example is different and doesn't work the same. Do I need a function in a module, or something, to make this work?

 

[theDBguy]

Hi. A Date/Time field always store both the date and time values together. If you only provide the date, then the time will be midnight. If you only provide the time, then date will be the epoch date, which I think is 12/31/1889.

Also, one thing to remember is formatting is not the same as the value. You can't do math with formatted data, but you can with their values.