Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: What is wrong with my iif and numeric data

    I have been through all the posts that I could using the search function but, could not find the answer to my question, so I registerd and figured I would post it.

    Here is the issue, when I issue the following

    SELECT [Join all Tables].[Business Unit]
    FROM [Join all Tables]
    WHERE ((([Join all Tables].[Business Unit]) Like IIf(IsNull([combo0]),"*",[combo0])))
    ORDER BY [Join all Tables].[Business Unit], [Join all Tables].City, [Join all Tables].[Server Name Align];

    I get the result I want, because it is a character field.

    But if I issue the same command against a "number" field, I don't get the results I expect, for example.

    SELECT SLA.SLA5
    FROM SLA
    WHERE (((SLA.SLA5)=IIf([var] Is Null,(SLA.SLA5)=1 Or (SLA.SLA5) Is Null,[var])));


    If my parameter matches the value in some of the SLA5 fields all is good. But, if the value is blank, I get no records.

    The result I would like to get is this. Check the toggle button and the value in "Var" will be 1, and if clicked, the value is null. I have this working great.

    But, I cannot get the damn select statement to work propperly. If there is a null value in the var parameter, I do not get any records, but I want to get all records if the value of var is null.

    Sorry for the long post, but I do not know how to explaine it better.

    Kyle

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Two things:

    using an IIF to determine alternate null values is clunky and hard to read, use nz().

    SELECT [Join all Tables].[Business Unit]
    FROM [Join all Tables]
    WHERE [Join all Tables].[Business Unit] Like nz([combo0]),"*")
    ORDER BY [Join all Tables].[Business Unit], [Join all Tables].City, [Join all Tables].[Server Name Align];

    Also:

    (((SLA.SLA5)=IIf([var] Is Null,(SLA.SLA5)=1 Or (SLA.SLA5) Is Null,[var])))

    That doesn't make sense.

    the iif statement attempts to assign TWO values to the same result if [var] is null. Do you mean to include "SLA.SLAS Is Null" as part of the where clause? If so, then it needs to be placed in a seperate criteria joined by the OR operator. Again I would recommend nz() instead of the iif, eg:

    WHERE (((SLA.SLA5)=nz([var],1)) OR (SLA.SLAS Is Null))
    Last edited by Teddy; 04-07-04 at 10:57.

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    Sorry, my last select statement was a test that I was doing and yes, it did make no sence.

    Here is what I need.

    I have a table with a field called SLA5.
    The table has 771 records in it.
    The value of SLA5 is either 1 or null.
    123 SLA5 records have a value of 1 in this table.

    I want to create a select statement, that when a value of 1 is passed to the select statement, it returns only the 123 records, but if the value of null is passed to the select statement then it will return 771 records.

    I can do this with a character/text field, but am having a hard time doing this with a numeric field.

    Teddy, your statement workes if you want to return all rows, but when you change the value of "Var" to 1, it does not return only the records with a value of 1.

    Kyle

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, I didn't understand what you wanted. You're bombing because the wild char is a string function. So convert your comparison to a string:

    WHERE STR(SLA.SLAS) LIKE nz([var], "*")

    Numbers should be implicitly converted to a string within the comparison, so don't worry about converting the values within the iif() statement.
    Last edited by Teddy; 04-07-04 at 16:22.

  5. #5
    Join Date
    Apr 2004
    Posts
    6
    Ok, one last try.

    So far the suggestions do not work for me. I do appriciate the help though.

    Here is what I need. I need a select statement that asks for a value, if that value is null, I need to return all records, if the value is not null, then return all records with a value in this field.

    The field is a numeric type, and this is what is causing the problem.

    Kyle

  6. #6
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    Cant the select query just toggle between 1 and 0 instead of 1 and null?
    0 - return null recs.
    1 - return rec with value in this field.

    hold on...i think im getting what ur getting at.

    If the user inputs nothing into this parameter query, it should produce all records?

    and if the user inputs say, 3 into the query, it should output all records for which that field has value 3? or should it output all records for which that field has any value?

  7. #7
    Join Date
    Apr 2004
    Posts
    6
    Originally posted by Jerrie
    Cant the select query just toggle between 1 and 0 instead of 1 and null?
    0 - return null recs.
    1 - return rec with value in this field.

    hold on...i think im getting what ur getting at.

    If the user inputs nothing into this parameter query, it should produce all records?

    and if the user inputs say, 3 into the query, it should output all records for which that field has value 3? or should it output all records for which that field has any value?
    Yes, the only value for the field is either 1 or Null, I have taken care of that with some code. It will be impossible for a user to enter anything other than a 1 in the field.

    I have tried the 0 to return all null records with no sucess.

    I can get the results I want, buy just using a simple query, but when I put the IS NULL in an IIF statement, it does not dissplay any records.

    I will post somemore examples tomorow. I cannot beleive that I am the only person who has had this issue, I guess nobody has been dumb enought to try it before me .

    Kyle

  8. #8
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    lol first things first, nothing is ever dumb or stupid. you had a purpose in doing this. this is defn. a solvable issue.

    so a. lets make it 0; that way, there isn't a null issue;

    this is just pseudocode.

    if combo1=0
    SELECT * FROM TABLE.
    else
    SELECT * FROM TABLE WHERE Field1=[Combo1];
    end if

    verify that this is true?

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    another question: does this happen when a user clicks a button? and the query results pop up? how does this code get implemented?

  10. #10
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    i think i figured out a solution for ya; quite simple actually;

    SELECT Inventor_Table.Inventor_ID, Inventor_Table.Nick_Name
    FROM Inventor_Table
    WHERE (((Inventor_Table.Inventor_ID)=IIf([Combo1]="0",[Inventor_ID],Int([Combo1]))));

    the key to that iif statement is..it returns the field that it points to if its 0; for e.g

    .....WHERE [Inventor_ID]=iif([Combo1]="0", [Inventor_ID], Int([Combo1]));

    so in a more general sense,

    ...WHERE [Field]=iif([VerifyField]="0", [Field], Int([VerifyField]));

    that way, [Field]=[Field] whenever its 0, so it returns all the Field records! ingenious eh?

    quite a tricky problem; i assure you, this is not the normal easy to solve problem...you did have a unique situation here.

    Thx!

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Or if you don't want to convert everything to 0... here's a different angle.

    SELECT SLA.SLA5
    FROM SLA
    WHERE ([var] IS NULL AND SLA.SLAS IS NULL) OR (SLA.SLAS IS NOT NULL AND [var] IS NOT NULL)

    Static values don't have to be included in the select list to be used in the where clause.

    ([var] IS NULL AND SLA.SLAS IS NULL) will only return records where the given parameter is null, AND the field is null.

    (SLA.SLAS IS NOT NULL AND [var] IS NOT NULL) will only return records where the given parameter is not null AND the field is not null.

    Since [var] is a variable supplied by the user, and only has two possible values, null and 1 then only one of these conditions will ever be true.

    That was a wierd one for me to understand what you were asking for.

  12. #12
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    correct me if im wrong, but if the field is not null, i think he wanted it to check against the field value.

    if [val] is null or "0", give all field values.
    if [val] is not null, then field value = [val]

  13. #13
    Join Date
    Apr 2004
    Posts
    6
    Well, I/We finaly got it to work.

    Here is the query.


    SELECT SLA.SLA5
    FROM SLA
    WHERE ([var] IS NULL AND (SLA.SLA5 IS NULL or sla.sla5 is not null)) OR (SLA.SLA5 IS NOT NULL AND [var] IS NOT NULL)

    and now, it the var is blank, I get all records, and it var is = 1 then I get only the records with a value.

    Thank you everyone for your help I really appricate it.

    PS, Teddy, you were killing me as you had put [sla].[SLAS], but it should have been [SLA].[SLA5]. An "S" not a five, that one through me for a while.

    :0,

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ??????????

    ...(SLA.SLA5 IS NULL or sla.sla5 is not null)) OR...

    seems like a waste of energy?

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    i didn't know u wanted the 1 to mean all records...thats simple.

    Field1 = iif([Combo1] is null, [Field1], -1)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •