Derived field based on subtracting 2 dates and if 0 add 1

[lazy2k]

Derived Field value based on subtracting to dates and if the value is zero then return a 1 else return the value from subtracting the two dates.

Code :
Sub DateDiff()
If "FAX_DATE" - "INPUTDATE" = 0 Then
DerivedField(1)
Else
DerivedField(("FAX_DATE") - ("INPUTDATE"))
End If
End

ERROR is illegal arguments to subtract

I'm new to this. Any help would be appreciated

 

[CharlesCook]

You don't need to use a macro for this. Use a SQL Derived Field like this:

DECODE(([!]"tablename".[/!]"FAX_DATE" - [!]"tablename".[/!]"INPUTDATE"),
0,1,
([!]"tablename".[/!]"FAX_DATE" - [!]"tablename".[/!]"INPUTDATE"))

What this is saying is:

IF ([!]"tablename".[/!]"FAX_DATE" - [!]"tablename".[/!]"INPUTDATE") = 0
THEN
1
ELSE
([!]"tablename".[/!]"FAX_DATE" - [!]"tablename".[/!]"INPUTDATE")
END IF


As far as your macro goes I think this would be closer:

Sub DateDiff()

FaxDate = DateValue(Field$("FAX_DATE"))
InputDate = DateValue(Field$("INPUTDATE"))
DateDiff = (FaxDate - InputDate)

If DateDiff = 0 Then
DerivedField "1"
Else
DerivedField Str$(DateDiff)
End If

End

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[CharlesCook]

How did that work out for you?

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting