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

Negative values

Hello Everyone,
I am writing a view and I have some negative values coming out of some
fields in one of my tables. They should be there, but when i retrive data i
need them to be non negative. I want to know if there is a function or if
there is a way to get rid of negaive sign.
Thank you allIf all are negatives, multiply the value with -1. Otherwise, you can use the
ABS function. Refer to SQL Server Books Online for more details.
--
-- Anith
( Please reply to newsgroups only )

Needs Help in Making Database

Please see the detail of tables with fields in SQL server 2000.

TableStudent .Table Good Qualities
( It is a list of Students) (It is a list of Good Qualities)
1-Student Id (P.Key)1- Serial No. (P. Key)
2-Student Name 2- Good Quality

Table Bad Qualities .Table Future Plans
(It is a list of Bad Qualities) (It is a list of Plans)
1-Serial No. (P. Key) 1- Serial No. (P. Key)
2-Bad Quality 2- Future Plans

Table-Personality
1-Student Name/ID
2-Good Qualities
3-Bad Qualities
4-Future Plans

Data will store in Personality table like that

Student IDG.Q.No B.Q.NoF.P.No
101 3 2 1
101 4 5 NUll
101 8 Null Null
202 4 8 9
202Null 3 1

Required ResultNow I have to generate a report of One student and
sometimes of many students comprising of their Name, G.qualities, Bad
Qualities and Future Plan. To generate report User input will be only
student id.

I Need help in making relationship of these tables as well as in
Making a Query. Any suggestion to get the required result. Specially
please look at the Personality Table because I do not understand
Primary key for it. Should I just include the Serial No. as Primary
key.
Please make necessary changes to get the required result.

I shall be really thankful to you for your kind favor.Hi

It is better to post the DDL (Create table statements) for your tables
rather than a pseudo-schema

You should have student id in the personality table (as I assume this should
be static and names can change!) if Student -> Personality is a one to one
relationship, student id can be made the primary key of the Personality
Table. Your personality table should have foreign keys for Serial No in each
of the other tables.

John

"Waqas" <waqasdeura@.hotmail.com> wrote in message
news:d1bc2061.0406191203.16d22a43@.posting.google.c om...
> Please see the detail of tables with fields in SQL server 2000.
> Table Student .Table Good Qualities
> ( It is a list of Students) (It is a list of Good Qualities)
> 1- Student Id (P.Key) 1- Serial No. (P. Key)
> 2- Student Name 2- Good Quality
> Table Bad Qualities .Table Future Plans
> (It is a list of Bad Qualities) (It is a list of Plans)
> 1- Serial No. (P. Key) 1- Serial No. (P. Key)
> 2- Bad Quality 2- Future Plans
> Table-Personality
> 1- Student Name/ID
> 2- Good Qualities
> 3- Bad Qualities
> 4- Future Plans
> Data will store in Personality table like that
> Student ID G.Q.No B.Q.No F.P.No
> 101 3 2 1
> 101 4 5 NUll
> 101 8 Null Null
> 202 4 8 9
> 202 Null 3 1
> Required Result Now I have to generate a report of One student and
> sometimes of many students comprising of their Name, G.qualities, Bad
> Qualities and Future Plan. To generate report User input will be only
> student id.
> I Need help in making relationship of these tables as well as in
> Making a Query. Any suggestion to get the required result. Specially
> please look at the Personality Table because I do not understand
> Primary key for it. Should I just include the Serial No. as Primary
> key.
> Please make necessary changes to get the required result.
> I shall be really thankful to you for your kind favor.|||On 19 Jun 2004 13:03:17 -0700, Waqas wrote:

(snip)

>I Need help in making relationship of these tables as well as in
>Making a Query. Any suggestion to get the required result. Specially
>please look at the Personality Table because I do not understand
>Primary key for it. Should I just include the Serial No. as Primary
>key.

Hi Waqas,

I can understand that you don't understand this table. The design is
wrong. Each table has to have a primary key. No columns in the primary key
may allow NULL values. In this design, only the StudentID column disallows
NULLS, and this column by itself clearly is not a primary key. The design
error is that multiple independent n:m relations are stuffed together in
one table.

As I have a strong hunch that this is a homework assignment, I'll stop
here and leave it up to you to use my hints and figure out the correct
model. Apologies if I'm incorrect about this being homework.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||John Bell (jbellnewsposts@.hotmail.com) writes:
> It is better to post the DDL (Create table statements) for your tables
> rather than a pseudo-schema

My guess is that the guy does not have the DDL yet. This looks like
a class assignment to me. And those we are usually not very helpful
with...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Need to write value of session variable to SQL record

Each user who inserts a new SQL record from the FormView control needs to have their UserID in one of the fields of the record. I have the user ID stored in the Session("UserID") variable. I am having trouble finding the right way to get this done. I have tried using a hidden text box but I can't seem to assign the value. I have tried the Insert Parameters but that will not accept <%# Session("UserID") %> as a DefaultValue. Any ideas would be helpful. Thanks.

Try handling the ItemInserting event of your FormView where you can programmatically set the SqlDataSource.DefaultValue to your Session variable value. For example:

protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)
{
SqlDataSource1.InsertParameters["UserID"].DefaultValue = Session["UserID"].ToString();
}|||Worked great with "(" instead of brackets "[". Thanks!|||I'm glad it worked out. My example was in C#. I guess you needed VB sample instead.|||You could of also created a SessionParameter and done it without any code at all.|||

Motley wrote:

You could of also created a SessionParameter and done it without any code at all.

Brilliant!!!