Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Combo box: And / Or statement for Query criteria?

    Trying to set up a combo box on a form and not sure if I can do what I want.

    I have a table [Work] that has two columns [Day of the Week] and [Sales]

    I have a query [Workaggregate] that aggregates the data in the [Sales] column for either "Monday", "Tuesday" or a combination of "Monday and Tuesday". I can hard code this into the criteria section to return the result I want. In the case of "Monday and Tuesday" I used an {OR} clause.

    I've created a Combo box on a form that present the user with the same three options. For the query, I have pointed the criteria section to the combo box on the form. When the user selects either "Monday" or "Tuesday" from the combo box the query displays the correct information since it matches exactly the records in the table [Day of the Week] column

    My issues is with trying to get the query to display the results if the "Monday and Tuesday" option is selected. Is there a way to take a combo box action and turn it into either an {AND} or an {OR} statement?

    I tried using a two column combo box, with the second column set to a width of 0". The first column I set to display "Monday and Tuesday" and the second column to "Monday" OR "Tuesday" but it didn't like all the quote marks. My thoughts was that I could then reference the second column in the query.

    Thoughts?

    Nate

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Presuming this would be extended to all 7 days of the week, I'd use a multiselect listbox:

    Multi-Select Listbox

    In fact I'd use it anyway. Not sure of how to do exactly what you're asking.
    Paul

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Thanks... I try and push Access beyond simple tasks, and I personally didn't think there would be an easy solution to what I wanted.

    I thought of something else... Is it possible...

    I have my two column combo box. I have the property bound to column 2. I have:

    Col 1......................... Col 2
    Monday...................... Monday
    Tuesday..................... Tuesday
    Monday And Tuesday....

    For column 2, I tried to put in : "Monday" OR "Tuesday" :, but it didn't like the quotes. Is there a way to have the quotes appear in column 2 without Access freaking out? If I just put in : Monday OR Tuesday : Access interprets this as the text "Monday OR Tuesday".

    Nate

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I can't think of a way to do it that way. It has to do with how the query will treat the combo reference.
    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not an easy one! You can use regular expressions and the Like operator to solve the problem:

    1. Table: TableDays
    -------------------
    Code:
    Field1	                | Field2
    ------------------------+-------------------
    Monday	                | Monday
    Tuesday	                | Tuesday
    Monday or Tuesday	| [M-T][o-u]*day
    2. Combo: Combo0 on Form50
    -----------------------------
    - ColumnCount: 2
    - ColumnWidth: 4cm;0cm (convert to inches if necessary)
    - BoundColumn: 2
    - RowSourceType: Table/Query
    - RowSource: TableDays

    3. Sample data table: Table11 (Field2 contains Day names)
    -----------------------------

    4. Query:
    ---------
    Code:
    SELECT *
    FROM Table11
    WHERE (Table11.Field2 Like [Forms]![Form50]![Combo0]);
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •