Elapsed Time among multiple records

[Heythere82]

Hi

Background

I'm building a database to track incidents. Depending on the nature of the incident, it might be immediately closed or left 'open' (unresolved) for a period of time.

I've be trying to create a dashboard form that displays the 10 most recent incidents, their status (closed or open), and the elapsed time (if open). I've done this through a continuous form feeding from a query.

The Problem

I can calculate the elapsed time (the current time MINUS the open time) and display it in a text box. However, all 10 text boxes display the same elapsed time because the calculation is only applying to the current record.

If I try creating a loop that will move between each record after calculating elapsed time, all 10 text boxes update to the current record instead of the one record it's referencing. So...

How can I reference individual records on a continuous form?


To be clear, I mean referencing the specific OpenTime text box that has information from record 1 instead of referencing the general OpenTime text box which will contain information from records 1 through record 10.


or Is there a better way of going about this?


Any help would be greatly appreciated!


Thanks

 

[Minty]

Calculate the elapsed time in the underlying query not the form, and requery as often as you need it updating.

 

[Heythere82]

Thanks Minty, but I am unsure of how to do this. I attempted this before, but I thought cell referenced functions (=Sum, =Current-Open, etc.) was something that could not be done in a table or query, even though they resemble excel datasheets.

 

[Minty]

You can calculate on fields. In the designer make a new calculated field;

ElapsedTime: Now() - [YourOpenTimeField]

Now you might need to do some clever formatting as I'm not sure if you need to display days/hours and or minutes - but in Access times and dates are actually stored as decimal numbers the bit on the left side of the decimal point represents days the bit on the right hours, minutes seconds.

 

[The_Doc_Man]

Please also note that this format works for times that are the difference of two date/time stamps (i.e. taken by [Stamp1]=Now(), ... then later [Stamp2]=Now(), ... then later [Elapsed]=[Stamp2]-[Stamp1]...)

You can do a Format$(date-diff-var, "hhhh:nn:ss") where nn is the format template for minutes - because "mm" would be month-number. This would give you hours up to 9999, minutes, and seconds. If you don't care about seconds, just leave off the ":ss" and you would be fine. NOTE, however, that if you tried to use ANY of the named formats on that time that is a difference of times, it will give you date around January, 1900, because the standard date formatting cases don't deal with elapsed times, only absolute times.

 

[Heythere82]

I cant use the Now() function in a calculated field apparently. When I add it to the expression box, I get the following error message.

"The expression Now() cannot be used in a calculated column."

Time() doesn't work either.

It looks like I'm only able to use either a preexisting field or a one of the 11 Date/Time 'Built-In Functions'. (DateSerial, Day, Hour, Minute, Month, etc.)






Formatting is also an issue. When I tried a calculated field for the closed incidents [ClosedTime] - [OpenTime], I get an absolute time back (as The_Doc_Man mentioned).

I tried entering variations on the Format$(date-diff-var, "hhhh:nn:ss")
The_Doc_Man mentioned, but they dont seem to work. I am supposed to be entering them in the Format box on the property sheet?

 

[Cronk]

Funny. I've used Now() in calculated query fields many times. You sure your open time in the table is a date field? Try putting Now() in its own query field? If still not working, post a copy of your database, cut down if needs be.

Incidentally to handle the open time for closed events, use the function

Iff(nz(Closed,0)=0, Now(), Closed) - Open

where Closed and Open are the names of the start, end date/time fields.

 

[Heythere82]

Wow, rookie mistake. I was trying to add those formulas in a table instead of a query. That apparently makes a big different. I think tables only allow specific expressions.

I was also able to fix the formatting issue by just formatting in the original expression. I also found a work around for the branching midnight problem, although I still haven't tested that yet.

ElapsedTime: IIf(IsNull([CloseTime]),Format([OpenTime]-1-Now(),"hh:nn:ss"),Format([OpenTime]-1-[CloseTime],"hh:nn:ss"))

Thanks everyone.

 

[Minty]

Calculated table fields in Access are a relatively new thing - so no one here probably spotted that was where you were trying to do it.
Glad you are sorted !