Results 1 to 15 of 15
  1. #1
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28

    MS Access 2016 Query


    I have a table with a field that contains such items as: Dallas, Tampa, Honolulu, Denton, etc (i.e. cities) and Phrases like Convention Center, Parks, Code Compliance. I need information that is based on the exclusion of some of the entries in the field. So, I'd like to create a parameter query that will allow me to exclude what ever cities and phrases within the field. How can I do this?

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    If you have these values Dallas, Tampa, Honolulu, Denton in one field then you are using Multi-Value Lists

    It is best not to have these types of fields and create a table that contains a List of all Values.

    This then makes it much easier to exclude values when running a query.


  3. #3
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Quote Originally Posted by mike60smart View Post
    Hi

    If you have these values Dallas, Tampa, Honolulu, Denton in one field then you are using Multi-Value Lists

    It is best not to have these types of fields and create a table that contains a List of all Values.

    This then makes it much easier to exclude values when running a query.

    I am not sure that I understand but to clarify, I have a table that has these entries. I use it as a lookup table for my main table which captures more data. When I call up the datasheet for my main table, I see all the records and the various entries. So, what I want to do is create a query from my master table and filter out some of the entries in the selected field so that I can report on the remaining data in a report. That is, if my table contains data for City Departments and that table has a field called Cities and another field called Agencies, I'd like to filter records for Finance Department that do not include Denton, Dallas....and see the records for the remaining cities. In some cases, the filter will have 2 exclusions. In others, it may have more. Does that make sense?

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Ok then that can be achieved by using the following as the Criteria for the Column Cities

    Not In ("Denton", "Dallas")

  5. #5
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Quote Originally Posted by mike60smart View Post
    Hi

    Ok then that can be achieved by using the following as the Criteria for the Column Cities

    Not In ("Denton", "Dallas")
    ---------------------

    So, I could set it up like this: Not in [enter cities]? would I still use the quotation marks with comma to separate the cities?

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    No, the example I gave is for when you need to exclude multiple Cities. You cannot enter the City names when prompted.

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, I could set it up like this: Not in [enter cities]? would I still use the quotation marks with comma to separate the cities?
    You can use:
    Code:
    Not [Enter City]
    to exclude one city, but I do not know that you can exclude multiple cities at once using a parameter query.

    I think it might be better to include a Yes/No field in this table, and then open up a Form that lists all the records in your table, showing just the Name and this flag, and then can "Check/uncheck" the ones they want, and then use that flag in the query where you want to return the information on your selected records.

    Alternatively, you could create a separate table that they populate with all the values that they want to exclude, and then do an Unmatched Query between this table and your other table to return just the records that you want.

  8. #8
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    I see. So, I need to revise the criteria in DESIGN every time.

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Not is you choose one of the other two options I recommended.

    A third option, which I use quite often, is to create a Form where they can enter the criteria.
    Then, you have VBA behind the form which builds the SQL code for that query based on their selections, assign it to the query, and then open the query.
    Note that you can see the SQL code for any query you create by going to the query and opening it in SQL View. This is helpful in knowing what the SQL code you need to build should look like.

  10. #10
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Thanks for your help.

  11. #11
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi JoeM

    I would be very interested in your VBA to allow the selection of Multiple Values if you are willing to share.

  12. #12
    cmiyatake is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    28
    Unfortunately, I have no VBA experience. So, I will need to modify the design query to accomplish what I require. Thanks for giving me that option, however.

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, here is a simple example of using the Form method:

    1. First, create a query which does everything you want, except for this dynamic query parameter. So, select all the fields you want, doing any caclulations, joins, other criteria that doesn't change. Let's name this Query1.
    2. Then, create a query and name it Query2. In this query, select Query1, click * to add/display all fields, and enter an example of the Criteria you want to build, like excluding two specific cities. Then, change to SQL View, and copy and paste the SQL code to a Word or text document for reference (this is what the SQL code that we want to build in VBA needs to look like).
    3. Now, create an Unbound Form, and add two objects to it: a TextBox named "txtExclude" and a command button named "cmdOpenQuery".
    4. Set the "On Click" property of the command button to run this VBA code:
    Code:
    Private Sub cmdOpenQuery_Click()
        
        Dim strSQL As String
        
    '   Build SQL string for second query (should be in same format of SQL code you copied from your Query2 example)
        strSQL = "SELECT * FROM Query1 WHERE City Not in (" & Me.txtExclude & ");"
    '   Temporary message box to confirm code written correctly
        MsgBox strSQL
        
    '   Assign SQL code to Query2
        CurrentDb.QueryDefs("Query2").sql = strSQL
        
    '   Open Query2
        DoCmd.OpenQuery "Query2"
        
    End Sub
    5. Now, just enter the cities you wish to exclude in the Text Box on the form, surrounded by double-quotes and separated by commas, i.e.
    Code:
    "Austin","Buffalo","Denver"
    and click the command button. It should open up the query results, excluding the cities you listed in the Text Box.

    If you wanted to get a little fancier, you could list the cities you want in separate fields, or select them from a list, and then loop through the list and build the string you need, adding in the double-quotes and commas where needed. It is really a matter of personal choice, how you would like them to enter/select the cities to exclude.

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Unfortunately, I have no VBA experience. So, I will need to modify the design query to accomplish what I require. Thanks for giving me that option, however.
    There isn't that much, and I pretty much gave you all that you will need above.
    Note that once you have it working correctly, you can remove the MsgBox line, so it won't pop up every time that you run it.

    Also note that neither of the the last two options I listed in my original reply require any VBA code.
    So you may multiple options to choose from, some with VBA, some without.

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi JoeM

    Many thanks

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

Similar Threads

  1. Replies: 3
    Last Post: 07-08-2019, 04:23 PM
  2. Replies: 2
    Last Post: 03-25-2019, 12:59 AM
  3. Replies: 4
    Last Post: 08-07-2018, 05:38 AM
  4. Automating Outlook 2016 from Access 2016
    By jcc285 in forum Programming
    Replies: 10
    Last Post: 09-30-2017, 01:53 PM
  5. Renaming Query and table in MS Access 2016
    By CHEECO in forum Access
    Replies: 3
    Last Post: 04-17-2016, 01:03 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