Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF Statement (2000)

    This is really annoying... not because of the query, but because i've seen a similar post in the last few weeks, but can't find it. <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>


    This is a simple iif statement in a query,

    <font color=blue>IIf([Forms]![Switchboard]![area]="RSC","RSC",IIf([Forms]![Switchboard]![area]="Manufacturing","MAN",IIf([Forms]![Switchboard]![area]="Site",([MEMOID].[mr])="MAN" Or ([MEMOID].[mr])="RSC","")))</font color=blue>

    I'm taking some text from a combo box on my form, and then applying the criteria in the query.

    If the text box = "Manufacturing", then have 'MAN' as the criteria in the query
    if the text box = "RSC" then have 'RSC' as the criteria in the query
    if the text box = "Site" then have either 'MAN or RSC" as criteria.

    This statement above is bombing out on the 'site' part. (query too complex.yadayda) I've seen a similar post earlier on about using the Switch function, but that appears just as awkward.

    I also tried putting the criteria on 2 lines, but i must have the syntax wrong.

    <font color=blue>IIf([Forms]![Switchboard]![area]="RSC","RSC",IIf([Forms]![Switchboard]![area]="Manufacturing","MAN",""))</font color=blue>
    <font color=blue>IIf([Forms]![Switchboard]![area]="Site",([MEMOID].[mr])="MAN" Or ([MEMOID].[mr])="RSC","")</font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF Statement (2000)

    Does this work? The table is meant to show the criteria for columns Forms!Switchboard!area and mr. You probabably want to uncheck the Display check box for the first of these.

    <table border=1><td align=center>[Forms]![Switchboard]![area]</td><td align=center>mr</td><td>"RSC"</td><td>"RSC"</td><td>"Manufacturing"</td><td>"MAN"</td><td>"Site"</td><td>"RSC" Or "MAN"</td></table>

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement (2000)

    Yup yup Hans, yet another <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> i owe you.

    (This offer excludes that <img src=/w3timages/censored.gif alt=censored border=0> known as Budweiser)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF Statement (2000)

    I'll settle for Sheperd Neame.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: IIF Statement (2000)

    Hey Hans

    What is Sheperd Neame ?

    Pat

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF Statement (2000)

    Sheperd Neame is a traditional brewery in Faversham, Kent (England), not far from Canterbury. Seemed appropriate since Steve is from Kent.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement (2000)

    Hans,
    I'm trying something similar and always anxious to learn. I'm not sure of your response. Can you elaborate a little more on your reponse? Thanks,
    Deb

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF Statement (2000)

    Steve wanted to select records with certain values in the "mr" field, but those values depended on the value of a text box on a form (it might have been another field in the query as well). This was his description:

    If the text box = "Manufacturing", then have 'MAN' as the criteria in the query
    if the text box = "RSC" then have 'RSC' as the criteria in the query
    if the text box = "Site" then have either 'MAN or RSC" as criteria.

    If you look at it the way he described, you are tempted to express the selection criteria as a series of IIf functions, but this soon becomes unwieldy, or even unworkable.

    Therefore I sought another way to look at it: as a series of conditions connected by And and Or. This is the description I came up with:

    Text box = "Manufacturing" AND mr = "MAN"
    OR
    Text box = ""RSC" AND mr = "RSC"
    OR
    Text box = "Site" AND mr = "MAN"
    OR
    Text box = "Site" AND mr = "RSC"

    Although this looks complicated to us humans, this is easy for the query engine - this is the kind of thing it is good in (because it was designed that way).

    In the query grid, you express AND conditions (both must be satisfied) by placing them in the same row, and OR conditions (at least one of the two must be satisfied) by placing them in the same column.

    This leads (in a slightly compressed way) to the table in my reply to Steve. Here is an expanded version. It is meant to represent part of the query grid.

    <table border=1> <td>Field:</td> <td>[Forms]![Switchboard]![area]</td> <td>mr</td> <tr> <td>Table:</td> <td>

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF Statement (2000)

    Thank you very much. You learn something every day. [img]/forums/images/smilies/smile.gif[/img]

    Thanks,
    Deb

Posting Permissions

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