469,327 Members | 1,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

subtract now() from date/time field in query

I'm trying to use this expression in a Query:


Expand|Select|Wrap|Line Numbers
  1. idleTime = now() - dateRequested 
It just spits out gibberish numbers with 20 decimal places though. I can't find a way to specify a date format in query design. Any ideas?

Here's the SQL view code:

Expand|Select|Wrap|Line Numbers
  1. Now()-[DateRequested] AS IdleTime
Feb 23 '07 #1
4 10394
ADezii
8,800 Expert 8TB
I'm trying to use this expression in a Query:


Expand|Select|Wrap|Line Numbers
  1. idleTime = now() - dateRequested 
It just spits out gibberish numbers with 20 decimal places though. I can't find a way to specify a date format in query design. Any ideas?

Here's the SQL view code:

Expand|Select|Wrap|Line Numbers
  1. Now()-[DateRequested] AS IdleTime
Expand|Select|Wrap|Line Numbers
  1. DateDiff("d",Now(), [DateRequested])    'difference in days
Feb 23 '07 #2
Thanks - Still learning :)
Feb 23 '07 #3
NeoPa
32,181 Expert Mod 16PB
I'm trying to use this expression in a Query:


Expand|Select|Wrap|Line Numbers
  1. idleTime = now() - dateRequested 
It just spits out gibberish numbers with 20 decimal places though. I can't find a way to specify a date format in query design. Any ideas?

Here's the SQL view code:

Expand|Select|Wrap|Line Numbers
  1. Now()-[DateRequested] AS IdleTime
Dates and Times are stored as numeric values.
To convert to a more meaningful string value use Format().
Expand|Select|Wrap|Line Numbers
  1. idleTime = Format(Now() - dateRequested,"0")
  2. or
  3. idleTime = Format(Now() - dateRequested,"HH:nn:ss")
The former displays the days and the latter the time. A single format string doesn't seem to work but they can be joined together with & if required.
Feb 26 '07 #4
NeoPa
32,181 Expert Mod 16PB
As a continuation, Your IdleTime field, as it stands, is the correct result anyway. It is simply formatted as a decimal number when displayed. It is fine as a store of the correct value. As long as you remember to display it correctly it is not wrong.
Feb 26 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by vnl | last post: by
3 posts views Thread by Russ Green | last post: by
1 post views Thread by Thomas Bartkus | last post: by
1 post views Thread by Jeffrey Melloy | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.