Major Brain Fart! -need help me with a time expression in a query 1

[nufather]

I am trying to figure out an expression that will take the current date and time and see if it is greater than a certain time + 24hrs. I have a table and 1 of the fields is date reported - all the records that are 24 hrs passed the date reported I want to know about.

 

[JimHorton]

If you just add 1 to the date, that will do it... ie:<br>
[Debug window]<br>
?(#01/30/2000 5:21:28 PM#) + 1<br>
1/31/00 5:21:28 PM <br>
...just compare ( Date Reported + 1) to the other dates.<br>
--Jim

 

[nufather]

If I have a query and under date reported as criteria i put in now()&gt;([datereported] +1) it doesnt come back with what I am wanting

 

[Lehtoant]

Calculate the difference between Now() and your date/time in hours with DateDiff function. Add an expression field in your query:<br>
<br>
DiffInHrs: DateDiff(&quot;h&quot;,[YourDateField],Now())<br>
<br>
And set the field's criteria &gt;24<br>
<br>
In SQL like:<br>
<br>
SELECT ...., DateDiff(&quot;h&quot;,[YourDateField],Now()) AS DiffInHrs<br>
FROM [Date]<br>
WHERE (((DateDiff(&quot;h&quot;,[YourDateField],Now()))&gt;24));<br>
<br>
Any release?<br>
Al

 

[nufather]

Al, This is exactly what I want - I am however not sure I understand how to do it in my query. PLEASe help - I have a table called callslog - the date/time field is called &quot;dateReported&quot; so if i open an new query and select to user the callslog table - I then do what? - where do I put in the datediff at, in criteria under date reported? - thanks for your help!!!! - I believe this is exactly what I want.<br>

 

[Lehtoant]

You're already halfway there, but here's the whole procedure:<br>
1. Make a query based on calllogs table<br>
2. Include all fields needed including your dateReported<br>
3. Go to next empty column and type this formula on the first (Field row <br>
DiffInHrs: DateDiff(&quot;h&quot;,[dateReported],Now())<br>
You can also right click and use Expression Builder to get formula right.<br>
4. When formula is ready go to criteria row of this column and add &gt;24<br>
5. Run the query<br>
<br>
So you will have an extra calculated field (DiffInHrs) which shows hours from present time (more than 24 hrs). If you want to hide the field, remove 'Show' check mark on the query grid.<br>
<br>
Al