Results 1 to 10 of 10
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    Using IFF Statement in a Query

    Hello,



    I am trying to use an IFF statement in a query. Basically, I have a column with the following:





    TYPE
    Elementary School
    High School

    Grocery Store
    Gamestop
    Gamestop
    High School

    Grocery Store.

    The column has a lookup field where the only options are Elementary School, High School, Grocery Store, and Gamestop

    I added this to a new query column RevisedType: IIf([Type]="Elementary School","Elementary")

    The IFF statement removes "Elementary School" and replaces it with just "Elementary". This is exactly what I want. However, when I run the query, High School, Grocery Store, and Gamestop do not appear in the ReviseType column. Any ideas why?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If type is Elementary, then ..... otherwise ....
    Add a comma at the end and [Type]

  3. #3
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I dont follow.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    IIf([Type]="Elementary School","Elementary",[Type])

  5. #5
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    to explain aytee's correction of your formula, a conditional needs to have two possible results: What to return if the condition is true, and what to return if it is false. Your formula told what to do if true, but you put no result for the false condition, so it gives you no result, a null.

    =Iif(Condition, ShowThisIfTrue, ShowThisIfFalse)

  6. #6
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Ohhh! Hahaha. Thank you!. That makes so much more sense now. I really appreciate it!

  7. #7
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    If I may ask one more thing, what if I want to have 2 IFF statements?

    IIf([Type]="Elementary School","Elementary") AND IIf([Type]="High School","High")

    Do I write
    IIf([Type]="Elementary School","Elementary",[Type], IIf([Type]="High School","High",[Type]))

    I do this, but recieve an error: "The expression you entered has a function containing the wrong number of arguments." It then highlights the last ')' that I used. When I remove it, it tells me I am missing a ')'

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Try
    Code:
    IIf([Type]="Elementary School","Elementary",IIf([Type]="High School","High",[Type]))
    NOTE: "Type" is an extremely poor name for a field. It is a reserved word (a field property); plus it is not very descriptive.



    The column has a lookup field where the only options are Elementary School, High School, Grocery Store, and Gamestop
    You should see
    The EVILS of Look up FIELDS in TABLES
    http://access.mvps.org/access/lookupfields.htm

    A look up table would be much better.........

  9. #9
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    Quote Originally Posted by zashaikh View Post
    If I may ask one more thing, what if I want to have 2 IFF statements?

    IIf([Type]="Elementary School","Elementary") AND IIf([Type]="High School","High")

    Do I write
    IIf([Type]="Elementary School","Elementary",[Type], IIf([Type]="High School","High",[Type]))

    I do this, but recieve an error: "The expression you entered has a function containing the wrong number of arguments." It then highlights the last ')' that I used. When I remove it, it tells me I am missing a ')'
    Iif([SchoolType]="Elementary School","Elementary",Iif([SchoolType]="High School","High",[SchoolType]))


    Edit, I read ssanfu's comment about using reserved words, but I hadn't seen his sample nested Iif before I posted this.
    Last edited by Preston; 07-21-2017 at 10:42 AM.

  10. #10
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Thank you! I appreciate this a lot.

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

Similar Threads

  1. SQL Statement in Query
    By EcologyHelp in forum Access
    Replies: 3
    Last Post: 11-16-2015, 10:38 PM
  2. Query IIf statement help
    By mike760534211 in forum Queries
    Replies: 5
    Last Post: 01-13-2014, 07:51 PM
  3. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  4. Replies: 3
    Last Post: 07-10-2012, 05:23 AM
  5. Query/IiF statement
    By peacepower in forum Queries
    Replies: 1
    Last Post: 08-23-2011, 04:05 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