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

[mpgoodman77]

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?

[JoeM]

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).

[mpgoodman77]

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!

[KathyL]

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

[mpgoodman77]

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.

[JoeM]

I would trying doing a LEFT JOIN from your main table to your lookup table, and have your equation something like this:

=Sum(IIf(NZ([Lookup_Table]![zip],"Not found")<>"Not found",([STHours]+[OTHours]+[DTHours]),0))