I have the following code

-*SET ECHO='ALL';

DEFINE FILE AE_DT1
	VENDOR_NUMBER/I8 = EDIT(AE_DT1.AE_DT1.FIELD1) ;
	INVOICE_NUMBER/I6 = EDIT(AE_DT1.AE_DT1.FIELD2) ;
	SCAN_DATE/A8YYMD = EDIT(AE_DT1.AE_DT1.FIELD24, '9999$99$99') ;
END

TABLE FILE AE_DT1
PRINT
	AE_DT1.AE_DT1.FIELD5 AS 'INVOICE_DATE'
	AE_DT1.AE_DT1.FIELD6 AS 'AMOUNT'
	SCAN_DATE
BY VENDOR_NUMBER
BY INVOICE_NUMBER
WHERE (AE_DT1.AE_DT1.FIELD2 EQ '38286');
-*WHERE SCAN_DATE GE '20110201' AND SCAN_DATE LE '20110216'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS TBL1
END
-RUN

-*?FF TBL1

DEFINE FILE F0411
-*	ADDRESS_NUMBER1/I8 = EDIT(ADDRESS_NUMBER) ;
-*	INVOICE_NUMBER1/I6 = EDIT(INVOICE_NUMBER) ;
	BATCH_DATE/A8YYMD = BATCH_DATE ;
END

TABLE FILE F0411
PRINT
	INVOICE_DATE
	GROSS_AMOUNT
	BATCH_DATE
	G_L_DATE
BY ADDRESS_NUMBER
BY INVOICE_NUMBER
WHERE (INVOICE_NUMBER EQ '38286');
ON TABLE HOLD AS TBL2
END
-RUN


JOIN VENDOR_NUMBER AND INVOICE_NUMBER IN TBL1
   TO ADDRESS_NUMBER AND INVOICE_NUMBER IN TBL2
END

DEFINE FILE TBL1
DIFF/YYMD = DATECVT(SCAN_DATE,'A8YYMD','YYMD');
DIFF2/YYMD = DATECVT(BATCH_DATE,'A8YYMD','YYMD');
DIFF3/D20 = DATEDIF(DIFF2, DIFF, 'd');
END

?FF TBL1
TABLE FILE TBL1
PRINT VENDOR_NUMBER
INVOICE_NUMBER
INVOICE_DATE
AMOUNT
SCAN_DATE
BATCH_DATE
DIFF
DIFF2
DIFF3
WHERE (INVOICE_NUMBER EQ '38286');
END
-RUN
-EXIT  

I get the following output

VENDOR_NUMBER INVOICE_NUMBER INVOICE_DATE AMOUNT SCAN_DATE BATCH_DATE DIFF DIFF2 DIFF3
811583 38286 2004-04-06 295.26 2004/04/22 2004/04/29 2004/04/22 2004/04/29 0
1007597 38286 2002-04-30 28.00 2002/05/14 2002/05/20 2002/05/14 2002/05/20 0
1375605 38286 2004-04-09 26.23 2004/04/14 2004/04/20 2004/04/14 2004/04/20 0
5434801 38286 2011-02-01 40.40 2011/02/14 2011/02/15 2011/02/14 2011/02/15 0

I am not sure why the DIFF3 is 0.

Please help.

This message has been edited. Last edited by: Kerry,

What happens if you first put the result in a hold file and do the final calculation of DIFF3 on that holdfile result.

Not sure if that works well and have not all the formula manuals here.

Without going any further, my first question is: Why the heck are you using THREE different date formats in the same file !! ?? It might be an idea to standardize on YYMD format from the get-go, after which the date comparisons happen in 3 or 4 words of code, not 10 lines worth !

The reason DIFF3 is getting zero is because you are using a lower case "d" in the function:

This works ...

  
DIFF3/D20  = DATEDIF(DIFF2, DIFF, 'D');