Nested IIF Statement

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Chris Waller
    New Member
    • Mar 2007
    • 2
    #1

    Nested IIF Statement

    Dear All,

    I am experiencing a problem with an IIF statement in Access. As I have never created one of these before I am having real problems getting my head around them. What I am trying to do is write an IIf statement that will bring out only information that I require and because I am asking for several things to be done there is a conflict somewhere and it just is not working.

    The statement I am using is below. The fields that I am using are "Cycle" and "Comment", but there are others, but not knowing which line or field to put this information in I am at a loss. Could some kind person please point me in the right direction as to how I would link a number of different fields and criteria.

    =IIF([cycle]="02" and [comment]="RFC11878") )
    Tags: None
    • Denburt
      Recognized Expert Top Contributor
      • Mar 2007
      • 1356
      #2
      Is this in a query, Controlsource, or VBA?
      I am asking for several reasons, anyhow your statement addresses the criteria but you left out the part to show that the criteria is met or not:

      Code:
      =IIF([cycle]="02" and [comment]="RFC11878","ThenDoThis","OtherwiseDoThis")
      The following can be replaced with field names and even calculations[ThenDoThis] + [ThenDoThis] or as a constant such as what I showed.
      "ThenDoThis "
      "OtherwiseDoThi s"

        Comment

        • Denburt
          Recognized Expert Top Contributor
          • Mar 2007
          • 1356
          #3
          I am still not awake yet need more coffee. I reread your title and if you need to nest them then it might look more like the following:

          Code:
          =IIF([cycle]="02" and [comment]="RFC11878",IIF(1=1,1,2),IIF(2=2,2,1))

            Comment

            • Chris Waller
              New Member
              • Mar 2007
              • 2
              #4
              FAO Denburt,
              Sorry If I din't make it clear. I am actually trying to do this on the query itself. What I am trying to do is to produce a report and if the records do not match the criteria then I don't want them counting in my query. Hope this helps.

                Comment

                • Denburt
                  Recognized Expert Top Contributor
                  • Mar 2007
                  • 1356
                  #5
                  So you are saying that you are buiding criteria for a field that will show results if the criteria is met

                  Code:
                  MyNewFieldName:IIF([cycle]="02" and [comment]="RFC11878",IIF(1=1,1,2),IIF(2=2,2,1))

                  or are you actually trying to create criteria to remove the records you don't want(looking at it in SQL view it would be a where or having statement depending if your query has totals):
                  Code:
                  Where [cycle]="02" and [comment]="RFC11878"

                    Comment

                    • MMcCarthy
                      Recognized Expert MVP
                      • Aug 2006
                      • 14387
                      #6
                      Chris

                      If you give us the logic in English of what you are trying to do with your query we will be beter able to help.

                      Mary

                        Comment

                        • missinglinq
                          Recognized Expert Specialist
                          • Nov 2006
                          • 3533
                          #7
                          Or if not in English, at least in American! ;0)>

                            Comment

                            Previous template Next
                            Working...