Date Calculations in Grids

[RRComputerGuy]

I am storing several dates in an SQL 2000 database. I am using a grid to display quantities and based on the stored
duedate - datecomplete I turn the quantity red or green for on time or late. In the grid field/expression I have entered the following code:

=if ([CoatASD] <= [CoatSSD]) &quot;<font color=green>&quot; + [CoatQty] + &quot;</font>&quot;; else &quot;<font color=red>&quot; + [CoatQty] + &quot;</font>&quot;;

The problem is it seems to be evaluating text.
Example:

CoatASD = 2/5/02
CoatSSD = 2/11/02
CoatQty = 10
Results is a Red 10 even though 2/5 is before 2/11. It seems to be evaluating each chr left to right 2 = 2, 5>1. I know this is probably a syntax issue, but I have scoured the net looking for this problem with no luck.

Thanks

 

[MerlinB]

Your best bet is to add a column in SQL that does the date maths in SQLServer:
... , datediff(day, CoatASD, CoatSSD) as AgeDiff

then your code becomes
=if ([AgeDiff] <= 0) &quot;<font color=green>&quot; + [CoatQty] + &quot;</font>&quot;; else &quot;<font color=red>&quot; + [CoatQty] + &quot;</font>&quot;;

or SQLServer could return 'Green' or 'Red' as the column value - or whatever would make your life easier!

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)