DateDiff results not comparing correctly

[mohebk]

Hi, (using MS Access 2003)
I created a query with a new field to calculate the difference between posting date and current date. The calculation seems to be working fin but when I add > 35 in the criteria, it has no affect. I expect the query to skip any records that have less than or = 35. But I see numbers less than 35 in that column. Please help.

Thanks
Mo

Mo

 

[PWise]

please post the SQL

 

[mohebk]

Sorry,
Please see below:

SELECT T207272.FSC_GROUP, T207272.FSC, T207272.PATNAME, T207272.INVNUM, T207272.SERDATE, T207272.REJCODE, T207272.POSTDATE, Date() AS today, DateDiff('d',[T207272].[POSTDATE],Date()) AS Dif, T207272.CHGS, T207272.BAL, T207272.REJ1, T207272.REJ2, T207272.REJ3, T207272.REJ4
FROM T207272
WHERE (((T207272.REJCODE) Is Not Null) AND ((T207272.REJ1) Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315')) AND (((DateDiff('D',[T207272].[POSTDATE],Date())))>35)) OR (((T207272.REJ2) Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315'))) OR (((T207272.REJ3) Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315'))) OR (((T207272.REJ4) Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315')));


Mo

 

[Golom]

Can't be certain but I suspect that it has to do with the mixture of AND and OR operators. I removed some of the parentheses and got this

SELECT FSC_GROUP, FSC, PATNAME, INVNUM, SERDATE, REJCODE, POSTDATE, Date() AS today, 
       DateDiff('d',[POSTDATE],Date()) AS Dif, CHGS, BAL, REJ1, REJ2, REJ3, REJ4

FROM T207272

WHERE [red]REJCODE Is Not Null 
  AND REJ1 Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315') 
  AND DateDiff('D',[POSTDATE],Date())>35 [/red]

      [blue]OR REJ2 Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315') 
      OR REJ3 Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315') 
      OR REJ4 Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315')[/blue];

"AND" is processed before "OR" so this will generate an intermediate expression like

WHERE [red]FALSE[/red]

      [blue]OR REJ2 Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315') 
      OR REJ3 Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315') 
      OR REJ4 Not In ('R308','R311','R700','R701','R702','R800','R900','R314','R315')[/blue];

Then the "OR" clauses are evaluated and, if any of them returns TRUE then the final evaluation might be

WHERE [red]FALSE[/red]

      [blue]OR TRUE OR FALSE OR FALSE[/blue];

And that will return TRUE even though the first expression (the one containing your date test) was FALSE. The record then gets included even though it didn't meet the

DateDiff('D',[POSTDATE],Date())>35

test.

 

[mohebk]

Thank you. You gave me enough hints to figure it out. I repeated the >35 with each of the "or" clauses and it worked great.

Thanks for your help.


Mo

 

[lespaul]

you might also try it like this:

where dateDiff(blah blah) > 35 AND (
(REJ2 NOT IN(all those numbers)) 
OR 
(REJ3 NOT IN (all those numbers)) 
OR 
(REJ4 not in (all the other numbers))
)

that way all the REJ stuff is inside the AND parens and the datediff is applied first to all records...


Leslie

Have you met Hardy Heron?