Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    11

    Exclamation Unanswered: iif statement in a query?

    More problems with invoicing!

    This time, I need to implement an 'iif' statement within a query so that the following occurs:

    There is an 'yes/no' check box, if 'yes' is checked then a certain message needs to appear in a field and if 'no' is checked an alternate message need to appear in the same field.

    =IIf([Paint Cabin]="-1","Paint Cabin","DO NOT PAINT CABIN"), when I use this statement in a calculated textbox, it works, but thats no good to me as I need to store the results in a query. When I use the same statement in a query it returns an error in the field.

    I need the results stored in the field because I need to implement another statement in another query field that concatenates all the checked items in a field called 'description'.

    =[a] & "," & [b] & "," & [c] (where a, b and c represent the names of the text boxes). That equation works in a calcualted text box, but when I implement it in the query field, it returns an error.

    The reason I need to store these values in the query is because, I need to generate a report using that query with all those fields.

    I hope I explained it clearly. Any suggestions!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    assuming your data is in access .mdb

    it's not recommended to use -1 ...use the intrinsic access constant True
    "-1" is even more wierd: stay well away from that one (tho it can work in a textbox as you discovered, since the contents of a textbox can be interpreted as text).

    either of:
    =IIf([Paint Cabin]=True,"Paint Cabin","DO NOT PAINT CABIN")
    =IIf([Paint Cabin],"Paint Cabin","DO NOT PAINT CABIN")
    should do nicely.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or a variation on the theme
    =IIf([Paint Cabin]=FALSE,"DO NOT ","") & "Paint Cabin"

    However As a general rule I would be cautious over using IIF in a query. Used sparingly its not a problem and can be convenient,however if you use the construct frequently in a query which can return many rows there can be a significant perfomance hit. You can achieve the same functionality by placing a function call or some code in the reports detail format event or forms on current event.

    For the same reason I would try to avoid using domain functions (Dlookup etc)

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Quote Originally Posted by platinumblak
    I need the results stored in the field because .....

    The reason I need to store these values in the query is because, I need to generate a report using that query with all those fields.

    I hope I explained it clearly. Any suggestions!
    Hi platinumblak,

    Just so you get your questions out a bit clearer, to make a point of the above statement....in the Query you are not actually [/b]Storing[/b] that data in a field, you are rather Calculating it. Data that is Stored in fields is the data actually stored in your table, i.e. CompanyName, EmployeeName, etc. Seems Izy took care of your situation as well.

    have a nice one to all,
    BUD

  5. #5
    Join Date
    Sep 2003
    Posts
    69
    try :


    newfield: IIf([Paint Cabin]="-1","Paint Cabin","DO NOT PAINT CABIN")

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    like i said "-1" is weird!! (ok, i got the vowels the wrong way around)

    -1 (number) works for access
    1 (number) works for SQL-Server

    i am not aware of any RDBMS that employs "-1" (text) as True

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by izyrider
    like i said "-1" is weird!! (ok, i got the vowels the wrong way around)

    -1 (number) works for access
    1 (number) works for SQL-Server

    i am not aware of any RDBMS that employs "-1" (text) as True

    izy
    izyrider is correct and just trying to get you in the habit of using the proper code/syntax, etc. Though there ARE many ways to make things work, there are also many IMPROPER ways to do things as well.

    BUD

Posting Permissions

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