Reporting Text Box - Binding to Value, Empty if Null, Customer Format if not

[Mark Jordan]

Hey All,

I have a text field that I need to format as a date.  Sometimes this field is null, in which case, I can just display an empty value.

I'd like to be able to do this:

=IIf(IsNull(ESR_UDF116, "") = "", "", IIf(Len(ESR_UDF116)=8, Substr(ESR_UDF116,4,2) + "/" + Substr(ESR_UDF116,6,2) + "/" + Substr(ESR_UDF116,0,4),""))

The logic being, if the value is null, then return an empty string.  If the value is not null, then do the formatting.  My formatting hinges on the value being 8 characters long, so I have another embedded IIf that checks the Len of the field.  And that is the problem.

Per the documentation, the functions are evaluated completely *first* and the results passed to the functions.  In this case, when my field is Null, the Len function throws an error and hoses up my report.

It seems logical to me that the functions would be evaluted in precendent order so that the Len call would *never be made* if the field value is Null.

Has anyone ever achieved this desired result?

For a null, show nothing, for a non-null, show a formatted result that involves evaluating on the field itself.

[Mark Jordan]

For future searchers...

I got it figured out, but it's not exactly clean.

I basically "protected" each of my string function calls (Len and Substr) with the IsNull function.  My formula ended up like this.

= IIf(Len(IsNull(R01_SHIFT_DATE, ""))=8, Substr(IsNull(R01_SHIFT_DATE,"        "),4,2) + "/" + Substr(IsNull(R01_SHIFT_DATE,"        "),6,2) + "/" + Substr(IsNull(R01_SHIFT_DATE,"    "),0,4),"")

Since my SubStr calls pull characters from specific locations, I had to ensure the default value (what was used if Null) was large enough to accomodate executing the function.