none
Multiple criteria search with a Select Query RRS feed

  • Question

  • Hello,

    I created a table with 4 columns.   I would like my processors to be able to search Column A for multiple values and return all records with those values.   Its a tax table that contains all the tax IDs that we work with.   My processors may have a request where they need 10 of those records found and all they have is the tax id (column A) to search so they can get the date from column B.   I have a basic understanding of Access, FYI

    Thanks

    Tuesday, November 29, 2016 7:47 PM

All replies

  • Well, without getting deep into coding, you might be able to use a Datasheet view to display the records and let your processors use the dropdown on the column header to seach for each tax ID. 

    Just a thought...

    Tuesday, November 29, 2016 8:08 PM
  • Hello .theDBguy,

    Thanks for the reply.   That would work, but not be effective as we can have hundreds of tax ID's to lookup.   I should have added that the tax ID's are copied from an excel document. 

    Thanks

    Tuesday, November 29, 2016 8:33 PM
  • Well, I was hoping to avoid using complicated solutions. The next best thing is to have the user "import" the Excel list of TaxIDs into a separate table and then either JOIN the table on your form to this new table or use an In() clause as criteria in a query for your form. For example, here's what it might look like in the Record Source of your form:

    SELECT * FROM OldTableName WHERE TaxID In(SELECT TaxID FROM NewTableName)

    Barring the above approach, the next step is to use VBA code.

    Hope it helps...

    Tuesday, November 29, 2016 9:03 PM
  • Hi Low Level Management,

    you had mentioned that,"I would like my processors to be able to search Column A for multiple values and return all records with those values"

    you had store the "Tax_ID" in column A of Excel file.

    you have 4 Columns Table in Access that have Tax_details.

    now you want to search in Excel file column and return the data from Access table. am I right? please correct me if am wrong.

    To search the data in Excel we need "Tax_ID". if you already have the Tax_ID that is going to be fetch the record from Access table then why you want to search in Excel file.

    do you want to check if the Tax_Id is exist in the  Excel file or not and then you can refer the code mentioned below to find data in Excel column.

    Sub FindDemo()
    Dim lCount As Long
    Dim rFoundCell As Range
    Set rFoundCell = Range("A1")
    For lCount = 1 To WorksheetFunction.CountIf(Columns(1), "Demo String")
    Set rFoundCell = Columns(1).Find(What:="Demo String", After:=rFoundCell, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
    With rFoundCell
    MsgBox ("Data Found...")
    End With
    Next lCount
    End Sub
    

    after this code you have to add code to access data from the table based on the TAX_ID.

    if you want further help regarding code to fetch data from table then let me know about that.

    if you want to follow the manual approach then you can use the method suggested by .theDBguy

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 30, 2016 2:47 AM
    Moderator
  • Hello Deepak,

    Im sorry, but your code talk is beyond my comprehension of code for access.   Im not sure that I am even explaining it correctly.  Let me get some screenshots so that you know exactly what I am looking at.  

    Here is the Sample Table in Question:

    My team would be given a list of Tax IDs to search against this table and return the Canadian_Revenue_Agency_Tax_Status for each searched Tax ID.   I can get it to work for one Tax ID at a time, but would like to be able to search multiple at one


    Wednesday, November 30, 2016 4:20 PM
  • Can't you just filter the form based upon a given Canadian_Revenue_Agency_Tax_Status which would return all Tax ID's for that Canadian_Revenue_Agency_Tax_Status? You could use a ComboBox dropdown control to have users select the Canadian_Revenue_Agency_Tax_Status they want.
    Wednesday, November 30, 2016 5:02 PM
  • Hi LLM,

    Did you also have a hard time implementing my suggestion of importing the list of Tax IDs into a temporary table?

    Would you rather the user just enters the list of TaxIDs in an Excel sheet and have the form look there for the criteria? If so, you can create a Linked Table to the Excel file and then JOIN the linked table to your Access table to display only matching TaxIDs.

    Hope it helps...

    Wednesday, November 30, 2016 5:11 PM
  • Good Morning Lawrence,

    I dont believe that would work, because my processors are not looking for all the Tax ID's with Favorable015 or Exempt.   They are looking for the status of specific tax ids that changes daily.   We currently have over 4000 records to search and can have up to 100 tax ids for each search.  

    Also, one thing to keep in mind with me and my team is that they have almost no training or experience with Microsoft Access and my experience has all been gained in the last two weeks as I built the database that I am currently working on. 

    Sorry if I am frustrating to work with, but this is all so new to me.

    Thanks



    Wednesday, November 30, 2016 5:52 PM
  • Hello .theDBguy,

    I did import a list of tax ids into the database, but I am still trying to learn what JOIN is and how to use it.  I will also try the LINKED table option that you mentioned and see what happens. 

    Thanks

    Wednesday, November 30, 2016 5:55 PM
  • I would like my processors to be able to search Column A for multiple values and return all records with those values.

    You can do this with a multi-select list box and some fairly simple code.  You'll find an example in MultiSelect.zip in my public databases folder at:

     

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

     

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

     

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

     

    In this little demo file the method illustrated via the top left button in the opening form is the model to follow.  In the ensuing form the user can select one or more items in the list box, and then open a report of all matches using the following code:

     

        Dim varItem As Variant

        Dim strEmployeeIDList As String

        Dim strCriteria As String

        Dim ctrl As Control

       

        Set ctrl = Me.lstEmployees

       

        If ctrl.ItemsSelected.Count > 0 Then

            For Each varItem In ctrl.ItemsSelected

                strEmployeeIDList = strEmployeeIDList & "," & ctrl.ItemData(varItem)

            Next varItem

           

            ' remove leading comma

            strEmployeeIDList = Mid(strEmployeeIDList, 2)

           

            strCriteria = "EmployeeID In(" & strEmployeeIDList & ")"

           

            DoCmd.OpenReport "rptEmployees", _

                View:=acViewPreview, _

                WhereCondition:=strCriteria

        Else

            MsgBox "No employees selected", vbInformation, "Warning"

        End If

     

    If the tax ID is a numeric data type all you need to do is amend the above to use the tax ID instead of the EmployeeID in my example.  If it's a text data type you'd need to wrap the values in literal quotes characters, which would require the following amendment to my code:

     

       strEmployeeIDList = strEmployeeIDList & ",""" & ctrl.ItemData(varItem) & """"

     

    While my demo opens a report it would need only minor changes to open a bound form, or to filter the current form if the list box were in a bound form rather than a separate dialogue form.



    Ken Sheridan, Stafford, England

    Wednesday, November 30, 2016 6:23 PM
  • Thanks, I will have to test at home, since my company wont allow me to open or download items from the internet.   I will let you know how it all works out.  
    Wednesday, November 30, 2016 7:21 PM
  • Hello .theDBguy,

    I did import a list of tax ids into the database, but I am still trying to learn what JOIN is and how to use it.  I will also try the LINKED table option that you mentioned and see what happens. 

    Thanks

    Hi,

    Hopefully, this link explains what JOINs are:

    Query Join Basics

    Good luck!

    Wednesday, November 30, 2016 7:27 PM
  • Hi Low Level Management,

    This work around can work for you and for your team who have basic knowledge for MS Access.

    I created one demo example for you.

    I create table like yours.

    then I create form.

    in first textbox you need to enter multiple Tax_id separated by comma.

    then click the button.

    and Tax_id and Tax_Status will be displayed in second textbox.

    on the click event of button I code like below. 

    Option Compare Database
    Option Explicit
    
    Private Sub Command2_Click()
    Dim strTest, ssql, str As String
       Dim strArray() As String
       Dim intCount, num As Integer
        Text3.Value = ""
       strTest = Me.Text0.Value
       strArray = Split(strTest, ",")
       
       For intCount = LBound(strArray) To UBound(strArray)
          Debug.Print Trim(strArray(intCount))
          num = CInt(Trim(strArray(intCount)))
          
          Dim con As ADODB.Connection
    
        Set con = Application.CurrentProject.Connection
    
        Dim rs As ADODB.Recordset
    
        Set rs = New ADODB.Recordset
    
        ssql = "select status from tax_detail where tax_id=" & num & ";"
    
        rs.Open ssql, con
    
        Do Until rs.EOF = True
            str = "Tax_ID = " & num & " ,Status = " & rs.Fields!Status
    
           Text3.SetFocus
           
           Text3.Text = Text3.Text + str + vbCrLf
           rs.MoveNext
    
        Loop
    
    
       Next
    
    End Sub
    

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 1, 2016 5:18 AM
    Moderator