Results 1 to 3 of 3
  1. #1
    kristyspdx is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2011
    Posts
    23

    Nested If/Or Statement


    Is there a way to write an if/or statement without having to repeat the field name every time. Example: I have a query that works as follows:

    IIf([Q61: Group]='Oper-Endow' Or [Q61: Group]='Desig-Endow' Or [Q61: Group]='Life Inc_Desig' Or [Q61: Group]='Oper-Life Inc' Or [Q61: Group]='Endow-Endow','620',IIf([Q61: Group]='Oper-Plant' Or [Q61: Group]='Desig-Plant','600',IIf([Q61: Group]='Oper-Loan' Or [Q61: Group]='Desig-Loan','630',IIf([Q61: Group]='34700-833xx','500',IIf([Q61: Group]='Oper-Desig' Or [Q61: Group]='Desig-Desig' Or [Q61: Group]='Oper-Oper','660',' '))))).

    I need to add more to this query, but Access limits the amount of string I can use and it's not enough. Is there a way to not use the [Q61: Group] everytime? I tried the following:

    IIf([Q61: Group]=('Oper-Endow' Or 'Desig-Endow' Or 'Life Inc_Desig' Or 'Oper-Life Inc' Or 'Endow-Endow'),'620',IIf([Q61: Group]=('Oper-Plant' Or 'Desig-Plant'),'600',IIf([Q61: Group]=('Oper-Loan' Or 'Desig-Loan'),'630',IIf([Q61: Group]='34700-833xx','500',IIf([Q61: Group]=('Oper-Desig' Or 'Desig-Desig' Or 'Oper-Oper'),'660',' '))))).

    But, I get errors when I try this.

    Thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    I don't know if In works (my gut is not):

    IIf([Q61: Group]In('Oper-Endow', 'Desig-Endow', 'Life Inc_Desig','Oper-Life Inc', 'Endow-Endow')

    This wouldn't be a good method anyway, as you don't want to code business logic in this way. I'd have a table with two fields (or add a field to an existing table), like Group and Code:

    Oper-Endow 620
    Oper-Plant 600

    and then you join this table into the query and you have your code. Much simpler and more dynamic. You don't want to edit your queries and everything when you get new groups, or codes change.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    1) You shouldn't use special characters or spaces in object names - only letters, numbers and/or the underscore. No one sees the names except the developer, so it doesn't matter (except readability) if you use "Q61Group" or "Q61_Group". (But it is easier if there are no spaces/special characters)

    2) If you have tables named "Q61: Group", "Q63: Group", etc , your structure is not normalized. You should read up on normalization before proceeding (IMO).

    3) In design view of the query, right click on the table and select properties. There is a field for ALIAS... Type in "A" (or whatever), add fields, then switch to SQL view it see what it look like.


    Paul's method is better, but aliasing a table can help sometimes......
    Last edited by ssanfu; 10-15-2012 at 05:13 PM. Reason: hit return too soon

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

Similar Threads

  1. Replies: 5
    Last Post: 04-20-2012, 06:40 PM
  2. Nested If statement
    By Bellablue in forum Access
    Replies: 7
    Last Post: 10-09-2011, 12:00 PM
  3. Nested IIf Statement
    By traquino98 in forum Queries
    Replies: 5
    Last Post: 06-11-2011, 10:56 AM
  4. Nested Iif statement help
    By Goodge12 in forum Queries
    Replies: 6
    Last Post: 09-21-2010, 11:45 AM
  5. nested if statement with two criteria
    By kendra in forum Queries
    Replies: 5
    Last Post: 06-16-2009, 04:07 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