Dates and Strings 1

[YANKRAY]

Using CR10

I am having trouble creating a formula that will give me a string result if I have a null value in a date field.

I am writing my formula as follows:

if isnull({PEM_KC135_OPEN_ITEM_MVW.CURRENT_PROM_DATE})
then "No EDD Date"
else {PEM_KC135_OPEN_ITEM_MVW.CURRENT_PROM_DATE}

I get the error that a string is required for my "else" date field.

How do I get my formula to work so where I have a null value for a date I get the words "No EDD Date"?

Thanks,
Ray

 

[rheard]

Try -

if isnull({PEM_KC135_OPEN_ITEM_MVW.CURRENT_PROM_DATE})
then "No EDD Date"
else totext({PEM_KC135_OPEN_ITEM_MVW.CURRENT_PROM_DATE})

The first part of your formula is trying to resolve to a string value, so the second part needs to as well - confused me for a while too!
This will allow you to use your string "No EDD date", but will turn your 'valid' datetime field into a string - so no datetime formatting can be done at this time.

Russ

 

[dfor]

try selecting a date greater then a date such as Jan 1, if there is null field it won't be selected. You can then do your calculation with valid data.