Help I'm stuck. Can anyone help me. I have a report that uses data from multiple date fields and I need a formula that will look at the 6 date fields and if the field is blank pull the previous field. I put a variable in the report but it is not recognizing it. This is my formula I'm using Crystal Reports X:
WhilePrintingRecords;
Global dateTimeVar DateBonusReached:=
DateBonusReached := (If IsNull ({EXTUWRKLOAN.Q6_DATE}) then {EXTUWRKLOAN.Q5_DATE} else {EXTUWRKLOAN.Q6_DATE});
DateBonusReached := (If IsNull ({EXTUWRKLOAN.Q5_DATE}) then {EXTUWRKLOAN.Q4_DATE} else {EXTUWRKLOAN.Q5_DATE});
DateBonusReached := (If IsNull ({EXTUWRKLOAN.Q4_DATE}) then {EXTUWRKLOAN.Q3_DATE} else {EXTUWRKLOAN.Q4_DATE});
DateBonusReached := (If IsNull ({EXTUWRKLOAN.Q3_DATE}) then {EXTUWRKLOAN.Q2_DATE} else {EXTUWRKLOAN.Q3_DATE});
DateBonusReached := (If IsNull ({EXTUWRKLOAN.Q2_DATE}) then {EXTUWRKLOAN.Q1_DATE} else {EXTUWRKLOAN.Q2_DATE})
This is the result I am getting:
Quote formula |
Q1_DATE |
Q2_DATE |
Q3_DATE |
Q4_DATE |
Q5_DATE |
Q6_DATE |
|
18-Aug-2014 |
|
|
|
|
|
|
27-Aug-2014 |
|
|
|
|
|
|
27-Aug-2014 |
|
|
|
|
|
|
27-Aug-2014 |
|
|
|
|
|
9/17/2014 12:00:00 AM |
12-Sep-2014 |
17-Sep-2014 |
18-Sep-2014 |
|
|
|
|
27-Aug-2014 |
|
|
|
|
|
8/30/2014 12:00:00 AM |
29-Aug-2014 |
30-Aug-2014 |
1-Sep-2014 |
02-Sep-2014 |
03-Sep-2014 |
04-Sep-2014 |
9/20/2014 12:00:00 AM |
19-Sep-2014 |
20-Sep-2014 |
|
|
|
|
9/12/2014 12:00:00 AM |
10-Sep-2014 |
12-Sep-2014 |
14-Sep-2014 |
16-Sep-2014 |
18-Sep-2014 |
|
9/5/2014 12:00:00 AM |
02-Sep-2014 |
05-Sep-2014 |
6-Sep-2014 |
14-Sep-2014 |
|
|
9/17/2014 12:00:00 AM |
15-Sep-2014 |
17-Sep-2014 |
19-Sep-2014 |
|
|
|
This is what it is supposed to look like:
Quote
formula |
Q1_DATE |
Q2_DATE |
Q3_DATE |
Q4_DATE |
Q5_DATE |
Q6_DATE |
18-Aug-2014 |
18-Aug-2014 |
|
|
|
|
|
27-Aug-2014 |
27-Aug-2014 |
|
|
|
|
|
27-Aug-2014 |
27-Aug-2014 |
|
|
|
|
|
27-Aug-2014 |
27-Aug-2014 |
|
|
|
|
|
18-Sep-2014 |
12-Sep-2014 |
17-Sep-2014 |
18-Sep-2014 |
|
|
|
27-Aug-2014 |
27-Aug-2014 |
|
|
|
|
|
04-Sep-2014 |
29-Aug-2014 |
30-Aug-2014 |
1-Sep-2014 |
02-Sep-2014 |
03-Sep-2014 |
04-Sep-2014 |
20-Sep-2014 |
19-Sep-2014 |
20-Sep-2014 |
|
|
|
|
18-Sep-2014 |
10-Sep-2014 |
12-Sep-2014 |
14-Sep-2014 |
16-Sep-2014 |
18-Sep-2014 |
|
14-Sep-2014 |
02-Sep-2014 |
05-Sep-2014 |
6-Sep-2014 |
14-Sep-2014 |
|
|
19-Sep-2014 |
15-Sep-2014 |
17-Sep-2014 |
19-Sep-2014 |
|
|
|
Edited by GENLISA - 21 Sep 2014 at 5:59am