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

    multiple choice list as a criteria in a query?

    Hello,




    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


    Alain

  2. #2
    Join Date
    Apr 2017
    Posts
    1,275
    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
    Code:
    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
    Code:
    ...
    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
    WEnd
    
    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
    Location
    Swansea,South Wales,UK
    Posts
    1,300
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  4. #4
    alain17 is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Apr 2016
    Posts
    11
    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