Results 1 to 6 of 6
  1. #1
    mpgoodman77 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    3

    Do I have too many conditions in my IIF Statement? I'm getting an error.


    I am using a long IIf condition in an Access report that summarizes hours worked based on certain zip codes.

    Here is my IIf statement:
    =Sum(IIf([zip]="90680" Or [zip]="90740" Or [zip]="90742" Or [zip]="90743" Or [zip]="92625" Or [zip]="92626" Or [zip]="92627" Or [zip]="92646" Or [zip]="92647" Or [zip]="92648" Or [zip]="92649" Or [zip]="92655" Or [zip]="92657" Or [zip]="92660" Or [zip]="92661" Or [zip]="92662" Or [zip]="92663" Or [zip]="92683" Or [zip]="92708" Or [zip]="92841" Or [zip]="92844" Or [zip]="92845",([STHours]+[OTHours]+[DTHours]),0))

    It returns an #ERROR on my report. There are 22 zip codes being tested for in this statement.

    If I remove some of the "Or" statements (I tried it with only 5) it works fine.

    Is there a limit to how many conditions I can have in one IIf statement?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I would make and use a lookup/reference table instead, and link to that. That would also make it easier to maintain in the future, should you have to add/remove entries (simply add/remove records from a table instead of editing formulas or code).

  3. #3
    mpgoodman77 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    3
    I already have a table called "Local Zips" that has all of the 22 special zip codes in one column. What would the format for the lookup statement be?

    Would it look something like:
    =Sum(IIf([zip]=VLookup [Local Zips] something,([STHours]+[OTHours]+[DTHours]),0))

    I'm a real amateur. I can't even find Lookup as a function to try to use!

  4. #4
    KathyL is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Aug 2010
    Posts
    94
    It's DLookup, not VLookup. VLookup is in Excel.

    Google DLookup and also refer to this: http://office.microsoft.com/en-us/ac...001228825.aspx

    I use lots of DLookups on forms and reports. But be aware they take a little processing time and you will see a visible pause on the forms when it's trying to display. If you can, join to two tables with a query and use the query as your form or report source, and it'll run faster.

    Kathy
    Access I.T. Consultant

  5. #5
    mpgoodman77 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    3
    OK - I found DLookup - but I still don't understand how to use it to sum hours in Table1 for only those Table1 zip codes that show up in my "Local Zip" reference table.

    I want to use DLookup instead of the IIf string in the argument below. My lookup table name is "Local Zip" and it now contains all of the zip codes listed in my IIf statement below.
    =Sum(IIf([zip]="90680" Or [zip]="90740" Or [zip]="90742" Or [zip]="90743" Or [zip]="92625" Or [zip]="92626" Or [zip]="92627" Or [zip]="92646" Or [zip]="92647" Or [zip]="92648" Or [zip]="92649" Or [zip]="92655" Or [zip]="92657" Or [zip]="92660" Or [zip]="92661" Or [zip]="92662" Or [zip]="92663" Or [zip]="92683" Or [zip]="92708" Or [zip]="92841" Or [zip]="92844" Or [zip]="92845",([STHours]+[OTHours]+[DTHours]),0))

    If someone would just get me started on how to fit the "Lookup" into my "=Sum(IIf..." statement, I can figure it out.

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I would trying doing a LEFT JOIN from your main table to your lookup table, and have your equation something like this:
    Code:
    =Sum(IIf(NZ([Lookup_Table]![zip],"Not found")<>"Not found",([STHours]+[OTHours]+[DTHours]),0))

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

Similar Threads

  1. Replies: 1
    Last Post: 06-20-2012, 12:44 PM
  2. Replies: 5
    Last Post: 04-20-2012, 06:40 PM
  3. Replies: 14
    Last Post: 02-08-2012, 03:36 PM
  4. iif statement with multiple conditions
    By ragsgold in forum Queries
    Replies: 7
    Last Post: 08-24-2011, 05:38 PM
  5. Iff Statement Meeting 2 Conditions
    By newbie in forum Access
    Replies: 11
    Last Post: 06-11-2009, 11:59 AM

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