iif problem brings back #Error

I'm pulling a "Number (8)" data type field for a date (formatted YYYYMMDD) from a Oracle 9i database. When the value is inserted into the database it is set a zero (A non-null database for the most part), otherwise it is for example 20061224. If the date field has a value the date is displayed correctly, but if it is zero then I get the "#Error" message.

Here is one of many iif expressions I've tried.

=iif(Len(CStr(Fields!DTE_MAILED.Value)) = 8,

((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4))

,

Nothing)

Now I've returned the value with out any formatting done to the string, and it will return "0"(zero) or a number. I've returned the lengths of the returning value and it comes back "1" or "8". I read a lot of previous posts and I thought at first that it was because I was trying to do a substring function on the zero value getting a index error. So I've changed the iif test condition many different ways with no prevail. I read a previous post where someone ended up doing his work in his SQL, but I would like to find out how to do this in the report. This is a simple expression, so I feel like there is something obvious I don't know maybe something with the format mask.

All help will be appreciated!

hi nwyork,

i don't have Oracle db so i can't try.

but as far as the error that i've encountered,

e.g. iif (condition, true exp, false exp)

you might want to put your true exp and false exp returning the same data types...

if your true exp has CStr then the false exp should put CStr

|||

Thanks for replying,

I thought that might be it so I tried returning (""), and after reading your post I tried using CStr function in the false part but I still get the error. I ran out of time so I used SubStr, Decode, and Length functions in the SQL.

|||

Hi nwyork

The first problem im seeing is in the boolean check.
You are using 2 expressions on the field.....(Cstr and Len)
If the field containes a null value there will be problems as
Len(Null) = #error

Why not try restructuring your iif statement as follows:

=iif( Fields!DTE_MAILED.Value = Nothing
,0
,((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4)))

I'm not sure if this is specific to your datasource as i've never used Oracle before but it should still do
the trick. You can even add a second iif in the false part of the expression to make sure that the length is 8
,because as soon as the expression hits false part you know that there is no
null's and you should not get an error when trying to use expressions on the the field value.

If I was unclear in any way, be sure to point it out.

It may be 2 late now but it may help when a similar problem pops up in the future ;P

G