Compare a Date/Time with current system time 1

[matpj]

A need to run a query that will display all records that are over 3 hours old.

I will be refering to a Date/Time field of the format dd/mm/yyyy hh:mm:ss

when I posted this request in this forum a while ago, someone told me to write:
>DateAdd("h",-3,Now())
in the criteria box.

This didn't seem to work.
Can anyone else suggest something?
also, whenever I try to sort the field in ascending order it only sorts by the dd part of the field, not by the Date and time together. Is there a chronological sort that can be run?

My main query is getting it to display only records over 3 hours old, so if anyone can help I would appreciate it.

thanks,
Matt

 

[tlbroadbent]

If you want times more than 3 hours old you need to check for '<' (less than) rather than '>'

mydate<DateAdd(&quot;h&quot;,-3,Now()) Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[bosully]

You can also try the DateDiff() function.

DateDiff(&quot;h&quot;, dteDate1, Now) > 3, will return true for any value of dteDate1 which is more than three hours older than the current time.

One thing to be wary of using this is that it works in whole hours and doesn't consider that more than three hours have passed until 4 hours have. That means that 3 hours and 59 minutes can return as not being greater than 3 hours! A more certain way is to use &quot;m&quot; for minutes and look for anything greater than 180.

Brendan

 

[matpj]

Terry,

I have tried using '<' instead of '>', but it doesn't seem to work properly

I get all results that are from the day before today, backwards.

and if I use the '>' I get results that are today onwards.

it seems to be looking at the Date part of the field, and not the Time.
Do you know why this would be?
should I paste my SQL for you to look at?

 

[tlbroadbent]

Yes. Please post your SQL. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[matpj]

Terry,

here is my SQL


SELECT [Problem Tickets].[Problem Type], [Problem Tickets].[Problem Description], [Problem Tickets].Status, [Problem Tickets].EV200_EVT_DESC, [Problem Tickets].Date
FROM [Problem Tickets]
WHERE ((([Problem Tickets].Status) Not Like 'Closed') AND (([Problem Tickets].Date)<DateAdd(&quot;h&quot;,-3,Now())));

 

[tlbroadbent]

The syntax looks correct if [Date] is a date/time column. If you've defined it as text with the format you showed in the first post the comparison will not work. You'll need another conversion.

SELECT
[Problem Tickets].[Problem Type],
[Problem Tickets].[Problem Description],
[Problem Tickets].Status,
[Problem Tickets].EV200_EVT_DESC,
[Problem Tickets].Date
FROM [Problem Tickets]
WHERE [Problem Tickets].Status <> &quot;Closed&quot;
AND Format([Problem Tickets].[Date], &quot;General Date&quot<DateAdd(&quot;h&quot;,-3,Now());
Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[matpj]

Terry,

I tried your code, above, as my date field was a text field.
It still does not work.

when I remove the date part of the SQL I get 11 results returned that are all Not Like 'closed'. All of these are over 3 hours old, but as soon as I put the Date criteria in, it only displays 7.

looking at the results, it seems to leave out any results whose 'day' part of the date is greater than today (i.e. 14)
so, again, even though I have used your code to change the format, it still looks at the day rather than the whole date/time.

any ideas?

if I change the Date format in the table design view (to General Date), will that change all poreviously saved records, and make this task easier?

 

[tlbroadbent]

Everything I've tried works on my computer. I have Access 2000 and Windows 98 SE. I've tried both US English and Bitish English date/time settings. I don't know if the problem occurs because of differences on your computer.

However, I do know that the problem would not occur if the [Date] column was defined as Date/Time. We get into big trouble when we try to format the data in the storage rather than storing data, such as dates, in native format. Date comparison with character data is always difficult because the collation is different for characters than numerics.

I changed a column from text (dd/mm/yyyy hh:nn:ss format) to date/time in Access 2000 and it converted properly. Make a backup before trying this yourself.

If not possible to change the data type, you may try one more query change.

Format([Problem Tickets].[Date], &quot;General Date&quot<Format(DateAdd(&quot;h&quot;,-3,Now()), &quot;General Date&quot

Good luck. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[matpj]

Terry,


thanks - its seems to work fine now.
I changed the format of the field to Date/Time rather than General Date, and it works ok.