Results 1 to 4 of 4
  1. #1
    SlowDog is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    2

    Iif statement does not pick up all the criteria

    [Code] ErrorCode1: IIf([Payment in Full Stop Code]=0 And [EscrowMTH]>0 And[Days Delinquent]<90 And DateAdd("m",12,[LAST ESCROW ANALYSISDATE])<[Report Date] And DateAdd("m",12,[NOTE DATE])<[First DueDT] And [FORECLOSURE STOPS]<>7 Or [Payment in Full Stop Code]=0 And[EscrowMTH]>0 And [Days Delinquent]<90 And DateAdd("m",12,[LASTESCROW ANALYSIS DATE])<[Report Date] And DateAdd("m",12,[NOTEDATE])<[First Due DT] And [FORECLOSURE STOPS]<>8 Or [Payment in FullStop Code]=0 And [EscrowMTH]>0 And [Days Delinquent]<90 AndDateAdd("m",12,[LAST ESCROW ANALYSIS DATE])<[Report Date] AndDateAdd("m",12,[NOTE DATE])<[First Due DT] And [FORECLOSURESTOPS]<>9,1,0) [/Code ]

    Above is my code. The criteria is below.
    Does not have Foreclosure Stop 7,8,9 or is not past due greater than 90days. Loan has escrow, and is also not a new loan booked within the past1 year.

    I am not sure what I am doing wrong. If I add the stop codes it does not make any difference.
    Thanks,




  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Does not have Foreclosure Stop 7,8,9 or is not past due greater than 90days
    That is not what the IIf conditions say - The condition string has all the expressions joined by AND for each of forclosure stop 7,8 and 9.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    The IIF() statement reformatted looks like:
    Code:
    ErrorCode1: 
      IIf(
      [Payment in Full Stop Code]=0 
      And [EscrowMTH]>0 And[Days Delinquent]<90 
      And DateAdd("m",12,[LAST ESCROW ANALYSISDATE])<[Report Date] 
      And DateAdd("m",12,[NOTE DATE])<[First DueDT] 
      And [FORECLOSURE STOPS]<>7 
      Or
      [Payment in Full Stop Code]=0 
      And[EscrowMTH]>0 And [Days Delinquent]<90 
      And DateAdd("m",12,[LASTESCROW ANALYSIS DATE])<[Report Date] 
      And DateAdd("m",12,[NOTEDATE])<[First Due DT] 
      And [FORECLOSURE STOPS]<>8 
      Or 
      [Payment in FullStop Code]=0 
      And [EscrowMTH]>0 And [Days Delinquent]<90 
      And DateAdd("m",12,[LAST ESCROW ANALYSIS DATE])<[Report Date] 
      And DateAdd("m",12,[NOTE DATE])<[First Due DT] 
      And [FORECLOSURESTOPS]<>9,
    1,
    0)
    In the VBA Order of Operations, "AND" has a higher Order than "OR" so "AND"s are grouped first then "Or"s.
    I would have used parenthesis to clarify the order.......

    You stated:
    Above is my code. The criteria is below.
    Does not have Foreclosure Stop 7,8,9 or is not past due greater than 90days. Loan has escrow, and is also not a new loan booked within the past1 year.
    I'm not sure about your criteria, but the query column might be written:
    Code:
    ErrorCode1: 
    IIf(
    (
      ([FORECLOSURE STOPS]<>7 AND [FORECLOSURE STOPS]<>8 AND [FORECLOSURE STOPS]<>9)
       OR
       [EscrowMTH]>0 And[Days Delinquent]<90 
    )
    AND [Payment in Full Stop Code]=0 
    And [EscrowMTH]>0 And[Days Delinquent]<90 
    And DateAdd("m",12,[LAST ESCROW ANALYSISDATE])<[Report Date] 
    And DateAdd("m",12,[NOTE DATE])<[First DueDT],
    1,
    0)
    without the formatting, it would be
    Code:
    ErrorCode1: 
    IIf((([FORECLOSURE STOPS]<>7 AND [FORECLOSURE STOPS]<>8 AND [FORECLOSURE STOPS]<>9) OR [EscrowMTH]>0 And[Days Delinquent]<90) AND [Payment in Full Stop Code]=0 And [EscrowMTH]>0 And[Days Delinquent]<90 And DateAdd("m",12,[LAST ESCROW ANALYSISDATE])<[Report Date] And DateAdd("m",12,[NOTE DATE])<[First DueDT] ,1, 0)

  4. #4
    SlowDog is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    2
    Oh, that makes sense. I thought it might be something simple. You explained it very well. Thank you. Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. If statement in query - criteria help!
    By fluffyvampirekitten in forum Queries
    Replies: 2
    Last Post: 10-14-2015, 04:09 AM
  2. IIF Statement with OR criteria
    By McArthurGDM in forum Queries
    Replies: 8
    Last Post: 03-16-2015, 05:01 PM
  3. Replies: 5
    Last Post: 03-02-2015, 09:52 AM
  4. If then statement using Yes/No criteria
    By jpiazza in forum Macros
    Replies: 5
    Last Post: 07-12-2014, 11:06 PM
  5. Help with two criteria in an IIF statement.
    By Orozvik in forum Queries
    Replies: 1
    Last Post: 04-26-2013, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums