Syntax error in Calculated Field

[udaykamble]

I am writing a calculated field in a report:

IF ([Query1].[Remaining Start Date]>_add_days([Query1].[Data Date],43)) THEN
0
ELSE
if ([Query1].[Remaining Finish Date]<_add_days([Query1].[Data Date],43))
then
( [Query1].[Remaining Units] )
else
( [Query1].[Remaining Units] * (43 - _days_between([Query1].[Remaining Start Date],[Query1].[Data Date]))/_days_between([Query1].[Remaining Start Date],[Query1].[Remaining Finish Date]) )


Error is :
A data type error ocurred at or near the position '164' in expression: 'IF ([Query1].[Remaining Start Date]>_add_days([Query1].[Data Date],43)) THEN 0 ELSE if ([Query1].[Remaining Finish Date]<_add_days([Query1].[Data Date],43)) then ( [Query1].[Remaining Units] ) else ( [Query1].[Remaining Units] * (43 - _days_between([Query1].[Remaining Start Date],[Query1].[Data Date]))/_days_between([Query1].[Remaining Start Date],[Query1].[Remaining Finis'. The operand types ('integer,decimal,double') for operator 'Conditional Expression' are not compatible.


Please help.

 

[blom0344]

My first tip would be :

use CASE expressions instead of If then else..

The output then always has to formulated between (), like:

case when ([Query1].[Remaining Start Date]>_add_days([Query1].[Data Date],43)) THEN
(0)
when
([Query1].[Remaining Finish Date]<_add_days([Query1].[Data Date],43)) 
then 
( [Query1].[Remaining Units] )
else
( [Query1].[Remaining Units] * (43 - _days_between([Query1].[Remaining Start Date],[Query1].[Data Date]))/_days_between([Query1].[Remaining Start Date],[Query1].[Remaining Finish Date]) )
end

Ties Blom

 

[udaykamble]

Thanks, Blom. I will test and let you know. Thanks for the quick response.