Results 1 to 4 of 4
  1. #1
    itprof74 is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Feb 2017
    Posts
    1

    How do I return all records in a Query when user types ALL in a combo box

    How do I return all records in a Query when user types "ALL" in a combo box?



    IIf([forms]![Form2]![Combo18]="ALL",([PASSTHROUGH CUSTOMER LIST].[NAGRP])="*",[forms]![Form2]![Combo18])

    If the user types "ALL" in Combo18 the list box returns nothing.

    Otherwise the list box is returning the specific selection in the combo.

    Thank you!!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    assuming your full criteria is

    WHERE [PASSTHROUGH CUSTOMER LIST].[NAGRP]=IIf([forms]![Form2]![Combo18]="ALL",([PASSTHROUGH CUSTOMER LIST].[NAGRP])="*",[forms]![Form2]![Combo18])

    then try

    WHERE [PASSTHROUGH CUSTOMER LIST].[NAGRP]=IIf([forms]![Form2]![Combo18]="ALL",[PASSTHROUGH CUSTOMER LIST].[NAGRP],[forms]![Form2]![Combo18])

  4. #4
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    another entirely different approach is to add a new field to the table, name the field ALL - and insert on every line as a default the value: All

    then in the query that is the source for the record set: add this field ALL and make it part of an OR criteria i.e. in query design view:

    both the [NAGRP] field and the [ALL] field have the criteria: [forms]![Form2].[Combo18] but of course on separate lines to be an OR not on the same line as that would be an AND

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

Similar Threads

  1. Replies: 9
    Last Post: 08-19-2014, 12:41 PM
  2. Replies: 2
    Last Post: 03-23-2014, 06:50 AM
  3. Replies: 1
    Last Post: 12-15-2011, 04:26 AM
  4. Replies: 4
    Last Post: 08-16-2011, 05:54 PM
  5. Possible to store user-defined types in table?
    By Binky in forum Programming
    Replies: 0
    Last Post: 11-20-2008, 02:28 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