Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a date field that is loaded called [Sched Complete Date]. Sometimes the field is blank (and it messes up some of my formulas). What I want to do is, when the Sched Complete Date is empty, add 45 days from another field in data; Create Date. Example:
Create Date = 1/1/2014
Sched Complete Date is updated to 2/14/2014
I can't figure out the structure of the "IF/Then" statement.
Thanks for any help.
Jeff
HI,
I don't think you cannot avoid IF:
if(len(trim( [Sched Complete Date]))>0, [Sched Complete Date], date(num([Create Date])+45) as [Sched Complete Date]
Good Luck!
Hey Boris:
I'm getting an error (red squiggles) after where the statement is entered. I can't figure out why.
Any idea?
Jeff
LOAD
`Age (Days)`,
`Assigned To`,
`CAPA No`,
Category,
`Close Date`,
`Date Open`,
`Days Open`,
Dept,
`Desc`,
Division,
Origin,
if(len(trim([Sched Completion Date]))>0,[Sched Completion Date],
date(num([Date Open])+45) as [Scheduled Completion Date],
Source,
Status,
Type,
Date(yearstart([Date Open]), 'YYYY') as YearOpen,
Date(yearstart([Close Date]), 'YYYY/MM') as YearClose,
Date(monthstart([Date Open]), 'YYYY/MM') as YrMon_Open,
Date(monthstart([Close Date]), 'YYYY/MM') as YrMon_Close,;
SQL SELECT *
FROM qryCAPA;
alt(
date([Sched Complete Date]),
date([Create Date]+45)
) as [Sched Complete Date]
closing bracket missed.
if(len(trim([Sched Completion Date]))>0,[Sched Completion Date],
date(num([Date Open])+45)) as [Scheduled Completion Date]
Sorry, missed ")"
if(len(trim([Sched Completion Date]))>0,[Sched Completion Date],
date(num([Date Open])+45)) as [Scheduled Completion Date],
Yeah, I just found it too. However, the data that is replacing the blanks is showing in a five-digit format.
41206, 41264, etc mixed in with the original dates.
How do I fix the format?
Jeff
Just add formatting:
if(len(trim( [SchedCompleteDate]))>0, [SchedCompleteDate], date(num([CreatedDate])+45, 'MM/DD/YYYY')) as [SchedCompleteDate],
I'm still getting the 5-digits.