Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How would I contruct the following IF statement:

    If Health Status = "Gain" and Product Category = "Two or More Products" and

    Dental indicator = "Y" or HMIG indicator = "Y"
    Then Health Members else 0

    =IIF([Health Status]="Gain", IIF([Product Category]="Two or More Products", IIF([Dental Indicator] ="Y" - here is where I am stuck. Do you put an OR because either one of those indicators could = "Y".



    Any help would be greatly appreciated. Thanks

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

    =IIF([Health Status]="Gain" And [Product Category]="Two or More Products" And ([Dental Indicator]="Y" Or [HMIG Indicator]="Y"), [Health Members], 0)

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Han's reply is correct if you are doing the conditional statement in a query - however if you are doing it in VBA, then it should be structured as:

    Code:
    If Health Status = "Gain" and Product Category = "Two or More Products" and (Dental indicator = "Y" or HMIG indicator = "Y") Then
    	Health Members = True
    Else
    	Health Members = False
    End
    Of course you would need to declare the variables, open recordsets or whatever.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd place parentheses around the or part:

    Code:
    If [Health Status] = "Gain" And [Product Category] = "Two or More Products" And _
    	([Dental indicator] = "Y" Or [HMIG indicator] = "Y") Then
      [Health Members] = True
    Else
      [Health Members] = False
    End If
    because And has precedence above Or unless overruled by parentheses. And you need square brackets around the field/control names.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='HansV' post='789768' date='19-Aug-2009 10:01']I'd place parentheses around the or part:
    because And has precedence above Or unless overruled by parentheses. And you need square brackets around the field/control names.[/quote]
    Good point! I've edited my response accordingly. Thanks.
    Wendell

Posting Permissions

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