Results 1 to 3 of 3
  1. #1
    tylerpickering is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Posts
    22

    Filter table based on form selection

    Hi All,



    I have a table, form, and query in my database. Currently my form options are IM, IR, and Both. The criteria is either IM or IR. I want to be able to filter the table based on the form selection so that if IM is selected, only those results appear, IR for IR, and both IM and IR if Both is selected on the form.

    This is the current formula that I have in my query:

    Code:
    IIf([Forms]![IMDB]![Equipment]="IM","IM",IIf([Forms]![IMDB]![Equipment]="IR","IR",IIf([Forms]![IMDB]![Equipment]="Both",UNSURE WHAT GOES HERE,"")))
    It functions correctly when selecting IM or IR individually, but I am not sure how to handle the last part. I know on a regular query I could just do ** In("IR","IM") ** but I am unsure how to handle it in the formula with the form.

    Any ideas?

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are IM and IR the only possible values in the field? That expression is in criteria row of query? Try:

    LIKE & IIf([Forms]![IMDB]![Equipment]="IM","'IM'", IIf([Forms]![IMDB]![Equipment]="IR","'IR'", IIf([Forms]![IMDB]![Equipment]="Both", "'*'","")))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tylerpickering is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2014
    Posts
    22
    Perfect, thanks! Didn't know the "Like" needed to go outside the if statement.

    I appreciate it!

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

Similar Threads

  1. Replies: 16
    Last Post: 06-02-2012, 06:11 AM
  2. Replies: 1
    Last Post: 09-06-2011, 01:47 PM
  3. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 PM

Tags for this Thread

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