Results 1 to 4 of 4
  1. #1
    alain17 is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Apr 2016

    multiple choice list as a criteria in a query?


    i have never used multiple choice list in Access

    Question about multiple-choice list fields: Obviously, using the "value" property of a multiple-choice list directly as a criteria for a query does not work.

    How can you easily use a multiple choice list as a criteria in a query?

    In other words, how to concatenate the various selected values and put them in criteria of a query?

    Should we write a function that concatenates these selected values with an "OR" between each value and use this function as a criteria?
    Or use an "IN SELECT (value1, value2 ....)?

    Thanks a lot for your help


  2. #2
    Join Date
    Apr 2017
    The IN () condition is a way to go I think. But there are 2 ways to do it.
    1. When you can create a subquery, which returns all values you need, the syntax will be
    SELECT ... FROM YourTable1 WHERE ConditionField IN (Select ConditionValue FROM YourTable2 WHERE ...)
    , and you can simply run the query, or you call it from some procedure or control event;

    2. When you can run a procedure, which returns a comma-separated string of all condition values you need, the code will be something like
    Dim QueryString AS String
    Dim ConditionList AS String
    Dim AllConditonsFound AS Boolean
    ConditionList = ""
    AllConditionsFound = FALSE
    While Not AllConditionsFound
         ConditionList = ConditionList & Iif(ConditionList = "", "", ",") & NextConditionExpression
         AllConditionsFound = ConditionCheckExpression
    QueryString = "SELECT ... FROM YourTable1 WHERE ConditionField IN (" & ConditionList & ")"
    Using QueryString: Run the query/Run a report with query as source/Update form's Record Source/Do whatever

  3. #3
    Join Date
    Jan 2017
    Swansea,South Wales,UK
    Please use # icon on toolbar when posting code snippets.
    Cross Posting:
    Debugging Access:

  4. #4
    alain17 is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Apr 2016
    Thanks a lot ArviLaanemets

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

Similar Threads

  1. multiple choice (with combobox? or list?)
    By ntambomvu in forum Forms
    Replies: 17
    Last Post: 04-29-2019, 08:46 AM
  2. Import from excel to multiple choice list
    By OroTrainee in forum Import/Export Data
    Replies: 5
    Last Post: 08-02-2018, 08:37 AM
  3. Replies: 2
    Last Post: 03-13-2018, 11:08 AM
  4. Making a multiple choice list searchable
    By dwheatley in forum Queries
    Replies: 8
    Last Post: 12-13-2017, 03:41 AM
  5. Replies: 0
    Last Post: 03-19-2017, 01:29 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