Time Left

[Pietro]

Hi,
I've a field on my query called 'Received' formatted as a general date
(2/28/2008 10:32:20 AM).
I want to add another field (Timeleft) to count how long time is left for
each record to complete a day and if the value is less than 0 it's displayed
as 00:00:00
Example
Received Time Now
Timeleft
4/05/2008 15:00:00 PM 4/05/2008 13:30:50PM 01:29:10

 

[Tom Wickerath]

Hi Pietro,

Is Time Now your current system time? Is Received a due date of some kind,
since it doesn't seem like a received value could be in the future? You
should be able to use the built-in DateDiff function to display the
difference between the Now() function and the Received value. If you want to
display any negative values as zero, you should be able to accomplish this
using the conditional IF expression.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Pietro]

Thank you Tom for your answer...
Time now is my system time,Received+24 hrs is my due date,i cannot use
the DateDiff function as it does not display the result in format hh:mm:ss

 

[Tom Wickerath]

Hi Pietro,

Sure you can....all you need to do is combine the Format, IIF, DateDiff and
DateAdd functions. Will something like this work for you? This assumes a
field named "Received" in a table named "tblJobs". Make the appropriate
substitutions if your field and/or table names are not the same. Here is the
SQL statement for a query:


SELECT [Received],
IIf(DateDiff("s",Now(),DateAdd("h",24,[Received]))>0,
Format(DateDiff("s",Now(),DateAdd("h",24,[Received]))/84600,"hh:nn:ss"),
"00:00:00") AS [Time Remaining]
FROM tblJobs;


You might also try experimenting with Doug Steele's Diff2Dates function:

A More Complete DateDiff Function
http://www.accessmvp.com/djsteele/Diff2Dates.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

Hi Pietro,

Sure you can....all you need to do is combine the Format, IIF, DateDiff and
DateAdd functions. Will something like this work for you? This assumes a
field named "Received" in a table named "tblJobs". Make the appropriate
substitutions if your field and/or table names are not the same. Here is the
SQL statement for a query:


SELECT [Received],
IIf(DateDiff("s",Now(),DateAdd("h",24,[Received]))>0,
Format(DateDiff("s",Now(),DateAdd("h",24,[Received]))/84600,"hh:nn:ss"),
"00:00:00") AS [Time Remaining]
FROM tblJobs;


You might also try experimenting with Doug Steele's Diff2Dates function:

A More Complete DateDiff Function
http://www.accessmvp.com/djsteele/Diff2Dates.html

If you want this to count down second by second, you'll need to put code
calling this query in a form's Timer event with the timer interval set to 1000
(milliseconds, to change the display once a second). Note that a running Timer
event will impact your database's performance and seriously mess up going into
design mode on any object in the database.