Nested IIF's

I need to evalute two different fields in some text boxes.

=IIF(Fields!BadAddressFlg.Value > Nothing,"Red",IIF(Fields!BadPhoneFlg.Value <> "G" OR Fields!BadPhoneFlg.Value <> Nothing, "Red","Black"))

If the valuefor bad address > nothing or Badphoneflg <> G or <>Nothing then the color needs to change to red, any of these can be true not all of them. I tried the switch, choose, all of them and if I take one of the fields out then I get it to work but I cant get them to work if they are both like this.

Thanks

Stokh

Three ORs mean that you don′t have to differ between the cases, right ?

=IIF(Fields!BadAddressFlg.Value > Nothing OR Fields!BadPhoneFlg.Value <> "G" OR Fields!BadPhoneFlg.Value <> Nothing, "Red","Black")

Did you already tried that ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Well you would think so but not in here it doesnt apparently that doesnt work either, it turns them all red

There is only one record out of the 13 returned in the report that meets both badaddress having data in it and badphone have N instead of G so that record should be red. 3 other records only have the N in badPhone so those should be red too, the rest should be all black. Instead it turns them all red!

I thought using Nested Iffs was supposed to evaluate each one seperate and that isnt the case I tried a switch statement too and that doesnt work either.

Stokh

|||I am not quite sure about the > Nothing, did you try the <> Nothing ? As Nothing is the equivalent for NULL I would be careful with direct comparing with it like a numeric value.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yeah I tried that to. Thats how the data comes in from the main frame which is not the way it should be but

I tried <> Nothing, Is Nothing, Trim(Fields!BadAddressFlg) you name I tried it. Nothing is working.

Stokh

|||

Here is the latest one, now if I take out this part "IIF(Trim(Fields!BadPhoneFlg.Value) <> "G" Or Trim(Fields!BadPhoneFlg.Value) <> Nothing,"Red" " I get the correct ones

=IIF(Trim(Fields!BadAddressFlg.Value) > " ","Red",IIF(Trim(Fields!BadPhoneFlg.Value) <> "G" Or Trim(Fields!BadPhoneFlg.Value) <> Nothing,"Red","Black"))

|||Strange thing. Do you have the chance to send over the sample data (as it is not very much, only those 13). You can create the appropiate data insert statements with the procedure here:

http://vyaskn.tripod.com/code.htm#inserts

As soon as you will post them I will create a sample project with it, we will get this thing to work :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Funny, there is only 13 records that are returned for this part there are 1000's of records in the table itself. and cant data privacy doesnt allow me to but I can give you the

appl_cd char 1

debtor_nbr varchar 10

debtor_nm varchar 50

debtor_xref_nm varchar 50

debtor_for nm carchar 50

address_line1 varchar 50

address_line2 varchar 50

city varchar 30

state_cd char 2

zip_cd char 5

zip_plus4_cd char 4

phone_nbr char 10

bad_address_flg char 4

bad_phone_flg char 1

last_update_dt datetime 8

Maybe that will help you not sure though.

Thanks!

|||

I got the answer

=iif(trim(Fields!BadAddress.Value) <> "" or (trim(Fields!BadPhone.Value) <> "G" and trim(Fields!BadPhone.Value) <> ""),"Red","Black")

apparently it come off the mainframe with not knowing if they added spaces or something into the fields. Not sure why they do it that way but nonetheless got it done.

Thanks for the help!

Stokh