List box used as parameter for query

[NewbieInCT]

Hello,



I have imported a list of companies from a source file I created. I want to use this list (one at a time) as the criteria to search my main database which I downloaded from the government website.

I want the query to do the following:

Select the name from my drop down list. Hit Run(or go, or process or submit), and up pops a report which tells me if the name appears.

I have spent all day working on this, and I guess I am slow for something that seems so simple.

I have the report working where it will show the results manually entering a Parameter, but I wanted the parameter to be from a list.

Working code with manually entered parameter:

 SELECT [Denied Party Query].[Consolidated Denied Party Report].[26]
FROM [Denied Party Query]
WHERE ((([Denied Party Query].[Consolidated Denied Party Report].[26]) Like "*" & [All] & "*"))));

so if I enter tent, I get results with impotent, or retention.... this is what I want.

Now, I want the drop down to do the same... just instead of typing tent, tent is showing on my list.

I am attaching a compressed version of my database.

On the flip, maybe easier, maybe not. if there is a way, with additional code, to have a macro or something that automatically runs each of the companies against the database and gives output in excel or whatever of my entire company list, that too would be great... basically, run a macro or code that does each name one at a time with an output... i could have 500, so I would get 500 different reports... Otherwise, I would be manually doing one at a time, as I am asking for above.[ATTACH]7745

[orange]

You have a table of companies that you created.And you have a table of companies you downloaded from a site.
You want to check if your companies are in the gov't table - is that correct?

[NewbieInCT]

tblCurrentCustomers is the table of the customers. the Consolidated Denied Party Report (Linked table) is the table from the US gov...

so I want to make a listbok which pulls from tbmcurrentcustomers and does a like and *......

[orange]

But you can do that without parameters and a bunch of reports if you have some unique fields.

To find the currentCustomers that are in the govt file

Select * from currentCustomers, LinkedTable
WHERE currentCustomer.SomeUniqueFld = LinkedTable.SomeUniqueFld

[NewbieInCT]

Orange, I sort of understand what you suggest, I just do not know how to implement.

I am attaching an updated file. if you have a moment, and could help modify, I would appreciate it greatly. I created a new query with the same SELECT and FROM and tried to modify the WHERE with the selection from the list from the Navigation page... but failed miserably.

I even found a multiselect listbox that someone posted in 2005, and tried to modify it, but I couldn't figure out how... maybe you can..

the multilist would be perfect if I could get the like feature to work...

it also seems like when I bring up the Navigation form I have some macro that keeps running in loops to run the query. I can't find this error eithermulti_select_listbox_2000.zip

[orange]

I don;t have 20100 I have 2003

[NewbieInCT]

Could you take a look? I would think the code from the list is from 2003, and works in my 2010.... I didn't write anything complicated.

[orange]

It looks good. I ran a few tests and seems fine. Was there something that wasn't working when you posted last? Seems fine now unless I've missed something basic.

One thing, when

If lstCounties.Column(0, i) = "All" Then
       flgSelectAll = True
'at this point you know All was selected
'so you can do 
      Exit For
'to bypass going through the For - Next Loop for no reason

In this case it isn't a large loop, but as a general practice --get out of the loop when you've logically ended

Another thing, if you don't have the free utility MZTools you should get it. Many useful features.
Available from http://www.mztools.com/v3/download.aspx

Get the vba.

I didn't realize you had an mdb in the zip/ Thanks and Good luck.

[NewbieInCT]

Hello, My question is how to change the query to say LIke and not IN. I want the code to search the entire string and find a partial word. Tent would result in impotent...

strSQL = "SELECT * FROM Sheet1"
    
    'Build the IN string by looping through the listbox
    For i = 0 To lstCounties.ListCount - 1
        If lstCounties.Selected(i) Then
            If lstCounties.Column(0, i) = "All" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & lstCounties.Column(0, i) & "',"
        End If
     Next i
     
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [strCompanyCounty] in (" & Left(strIN, Len(strIN) - 1) & ")"

I found this code online. I did not write it.

[orange]

OK back to square 1.
What do counties have to do with your issue?
You would not use like when you are dealing with County names.
So your sample code with the counties is just to make some sql based on a multi select listbox?

Please state your issue again, based on the code you found, and tell exactly

what will be in the listbox?
will it be multiselect?
what field(s) will you be comparing/looking for Like X?
=========================end of original ================================

A new look at your material (since you were offline and I wasn't likely to get answers before doing some tests)


I looked at your code again and revised the procedure to the following in order to get a Like clause.

Note, I am assuming you will use a multi select listbox --- using an All, and a number of strings

I am building SQL that will find records

Where somefield Like '*string1*' or somefield Like '*string2*'

note: string1 and string2 from your listbox selections.
There are commented debug.print statements that you can activate by removing the leading ' character
Most of my comments have a '..orange within the comment


NOTE: You can try this code in your database, but you should FIRST rename your procedure so it is not overwritten and lost/Gone/Deleted

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim YourFld As String
    YourFld = "TheFieldToLookIn " '.. orange
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
            ' Debug.Print "You selected " & vbCrLf & vbCrLf  '..orange
    Set MyDB = CurrentDb()
    
    strSQL = "SELECT * FROM tblCompanies"
    
    'Build the IN string by looping through the listbox
    For i = 0 To lstCounties.ListCount - 1
        Debug.Print "i  " & i & " " & IIf(lstCounties.Selected(i), "TRUE Selected " & lstCounties.Column(0, i), "FALSE  ") 'orange..
    If lstCounties.Selected(i) Then
            If lstCounties.Column(0, i) = "All" Then
            'if you Select All then No WHERE clause is needed  ..orange
                strWhere = "" 'make it 0 length string ..orange
                flgSelectAll = True
                Exit For
            End If
            'strIN = strIN & "'" & lstCounties.Column(0, i) & "',"
            '            Debug.Print "strIn " & strIN '..orange
            
            
            'New Code ---------------we know some thing was selected  and it wasn't All
            '=========
            strIN = strIN & " (" & YourFld & "LIKE '*" & lstCounties.Column(0, i) & "*') OR "
            
          End If
     Next i
  '=== This block commented ..orange
  '  'Create the WHERE string, and strip off the last comma of the IN string
  '  strWhere = " WHERE [strCompanyCounty] in (" & Left(strIN, Len(strIN) - 1) & ")"
  '====
  
  '  'If "All" was selected in the listbox, don't add the WHERE condition
   If Not flgSelectAll Then
   'remove the last OR from strIn
   strWhere = " WHERE "
    strIN = Mid(strIN, 1, Len(strIN) - 4)
        strSQL = strSQL & strWhere & strIN
    Debug.Print strSQL
   End If


' NOTE ***   This query won't work because I have a field that makes no sense/not in the table
   ' MyDB.QueryDefs.Delete "qryCompanyCounties"
   ' Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
    
    'Open the query, built using the IN clause to set the criteria
   '' DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
   
         'How many items were selected  ..orange
         Debug.Print " # selected items  " & Me.lstCounties.ItemsSelected.Count '..orange
    
    'Clear listbox selections after running query
    For Each varItem In Me.lstCounties.ItemsSelected
        Me.lstCounties.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:

   If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list", , "Selection Required !"
        Resume Exit_cmdOpenQuery_Click
    Else
    'Write out the error and exit the sub
        MsgBox Err.Number & " " & Err.Description
        Resume Exit_cmdOpenQuery_Click
    End If

End Sub

This code outputs sql for a query as follows: (This was for testing using your listbox and my choice of selections. Also if you choose ALL, no Where clause is added to the SQL.

SELECT * FROM tblCompanies WHERE (TheFieldToLookIn LIKE '*Dorset*') OR (TheFieldToLookIn LIKE '*Lincolnshire*') OR (TheFieldToLookIn LIKE '*Nottinghamshire*')

Post back with questions before you replace your proc code with the code given here.