DateAdd query criteria

[matpj]

I have a query that should disply records that are more than 3 hours old.
I am sure it used to work, but now appears not too.

my SQL code is as follows:


SELECT [Problem Tickets].[Problem Type], [Problem Tickets].[Problem Description], [Problem Tickets].Status, [Problem Tickets].EV200_EVT_DESC, [Problem Tickets].Date, [Problem Tickets].[Caller Reference], Format([Problem Tickets].[Date],"General Date&quot AS Expr1
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())));


can anyone offer any assistance?
thanks,
Matt

 

[MichaelRed]

It does not appear to be the time calculation, so there must be something else. The only other criteria expression is the [ <> &quot;Closed&quot; ] for the status. You probable need to post additional information, such as a FEW records which SHOULD be selected and a few which SHOULD NOT be selected from the table.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[matpj]

Michael,

out of all of the records currently on the system, there are two which should appear.
If I take the DateAdd expression out of the query in the design view, and then run it, these two records are displayed - but if I paste the expression back in the query, the two records are no longer displayed.
This leads me to think that it IS the DateAdd thing?!!!
The 'Date' field is in Date/Time format, so I cannot see any problems with this.

any other suggestions?

 

[MichaelRed]

Check the time on the clock(s). I have had &quot;users&quot; who's clocks were as much as a whole day off. If this is a problem, contract the Net Cop and haul 'him' into 'Time Court'. Get THE BOSS of Net World to tell him in plain terms that all db users (at the minunum) have their clocks set to Net Time at least once per day.


Also, check the parens grouping in te where clause. I have &quot;simplified&quot; it below, but tired old eyes occassionally miss these things.


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


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[matpj]

Michael,
I have noticed one area which is probably the cause.
Since I created the last query, I changed the field Data type from General Date to Date/Time.

When trying to change &quot;General Date&quot; to &quot;Date/Time&quot; in the SQL for my query, it seems to then revert to &quot;d&quot;&quot;ate&quot;&quot;/&quot;&quot;Ti&quot;&quot;m\e&quot; !!!! Even though I type in &quot;Date/Time&quot;!

how can I get around this?

 

[Databaseguy]

I don't know if this is the cause of your problem, but it;s one I've run against using dateadd()

The problem is, VBA doesn't like the &quot;&quot; inside of the dateadd() function.

What I've had to do is create a variable, called strFreq.
Dim strFeq as string
strFreq = &quot;h&quot;
-----

'later in the code
DateAdd(DateAdd(strFreq,-3,Now())))

HTH

 

[matpj]

Thanks for all your help.

I got over the problem by just replacing the :
(Format([Problem Tickets].[Date],&quot;General Date&quot

with ([Problem Tickets].[Date])

ie, not formatting it

It seems to work ok now!