This is driving me nuts!! how do you omit negative values in Access.

Specifically,

I'm calculating the days between dates. However, if value1 is later than value 2, the diffdate returns a negative value. I need to omit the negative values (or maybe return some sort of value like '0' or something.)

Help!!

Thanks a bunch.

To omit lines with negative values:

SELECT Table1.Value1, Table1.Value2, DateDiff("d",[Value1],[Value2]) AS Expr1
FROM Table1
WHERE (((DateDiff("d",[Value1],[Value2]))>=1));

To convert negative numbers to zero:

SELECT Table1.Value1, Table1.Value2, IIf(DateDiff("d",[Value1],[Value2])>=0,DateDiff("d",[Value1],[Value2]),0) AS Expr1
FROM Table1;

Hope this helps.


Clive Astley