Help with Query Criteria looking at multiple values from list box

pjmatelli

pjmatelli

Board Regular
Joined
Oct 13, 2005
Messages
206
Hey there,
I have a list box where users can select one or more values. Those values are stored in a text box separated by a comma. I have a query in which I need the criteria of the query to find a value if it is in the text box.

e.g. ReceiverNo (1234, 12345, 123) [Values stored from list box selection]

How do I use the criteria field of my query to look in that ReceiverNo field?

This is what I tried: In([Forms]![frmReconciliation].[ReceiverNo]) That doesn't seem to work?

Any ideas or other suggestions?????

As always, any help is greatly appreciated!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
You can't do it like that. If you want something like that you will have to build the SQL string in VBA. A very quick and painless way is if you download and place the SQLTools module that Access MVP Armen Stein has here. Then you can use the ReplaceWhereClause to do a quick switch.

Code: 
Dim strSQL As String
Dim qdf As DAO.QueryDef
 
strSQL = qdf.SQL
 
ReplaceWhereClause strSQL, "WHERE [FieldName] In(" & Forms!frmReconciliation.ReceiverNo & ")"
 
qdf.SQL = strSQL
 
qdf.Close
 
Set qdf = Nothing
 
Upvote 0
Thanks Bob,
I will give your suggestion a try and see what I can come up with. Do you have an additional recommendation as to where to place the code you are suggesting and how do I tell my query to use it?

Thanks again.
 
Upvote 0
pjmatelli said:
Do you have an additional recommendation as to where to place the code you are suggesting
Click to expand...
The code I showed would normally go in the click event of a button on a form which opens the query or opens the form or report that uses the query.

and how do I tell my query to use it?
Click to expand...

You don't need to tell it anything. Once you have modified the querydef with the new SQL, that IS the query. If you want it back to the original state then you would have to run similar code to remove the Where Clause we just set.
 
Upvote 0
Update:
Getting closer to making this thing work. But I have a question, in using the code you suggested, when I select one receiver (i.e. 29313) all is fine. Upon selecting the second receiver, here is what rLst looks like: (29413', '29413', 29414), which I believe is causing my run sql statement to bomb. Any idea why it grabs that first number twice???

TIA
 
Upvote 0
Sorry, posted the reply to the wrong topic... but here is the code that is grabbing the items selected in the box:

Code: 
 Private Sub RecNo_AfterUpdate()

  Dim strList As String
  Dim strFilterText As String
  Dim varItem As Variant
  Dim dbs As Database

  Set dbs = CurrentDb()
  strList = ""

  With Me!RecNo
    For Each varItem In .ItemsSelected
      strList = strList & .Column(0, varItem) & ","
    Next
On Error GoTo ErrorHandler
    strFilterText = Left(strList, Len(strList) - 1)
  End With
  Me.ReceiverNumber = strFilterText
  Me.frmAllocate.Visible = True
  Me.frmAllocate.Requery

  Dim j
    With Me.RecNo
        If .ItemsSelected.Count > 0 Then
            For Each j In .ItemsSelected
                rlst = rlst & "," & Chr(39) & .ItemData(j) & Chr(39)
            Next
        End If
        rlst = Mid(rlst, 2)
    End With

On Error GoTo ErrorHandler:


ErrorHandler:
Exit Sub
  
End Sub
 
Upvote 0
I'm not understanding why you have two for Each statements. It appears that you are iterating through the same thing.

Second, you are using Chr(39) instead of Chr(34) which I would use Chr(34) instead.

Can you put in comments into the code to share what you are doing with things and why?
 
Upvote 0
Bob,
Thanks for the quick response...hope the following helps...

Code: 
Private Sub RecNo_AfterUpdate()

  Dim strList As String
  Dim strFilterText As String
  Dim varItem As Variant
  Dim dbs As Database

  Set dbs = CurrentDb()
  strList = ""

  'The following with statement is taking the values that are selected in the list box
  'and placing them in another field (ReceiverNumber) to populate that field in the table without the single quote
  With Me!RecNo
    For Each varItem In .ItemsSelected
      strList = strList & .Column(0, varItem) & ","
    Next
      On Error GoTo ErrorHandler
    strFilterText = Left(strList, Len(strList) - 1)
  End With
  
  'After each item is selected, it updates the field with the new values
  Me.ReceiverNumber = strFilterText
  
  'Simply displaying the sub-form and getting new records
  Me.frmAllocate.Visible = True
  Me.frmAllocate.Requery

  'The following with statement is gathering the items that are selected in the list box
  'and populating a variable (rLst which is declared in the General Declarations area as a public variable)
  'so that it can be used in the SQL statement for the WHERE IN clause with the single quote
  Dim j
    With Me.RecNo
        If .ItemsSelected.Count > 0 Then
            For Each j In .ItemsSelected
                rlst = rlst & "," & Chr(39) & .ItemData(j) & Chr(39)
            Next
        End If
        rlst = Mid(rlst, 2)
    End With

On Error GoTo ErrorHandler:
ErrorHandler:
Exit Sub
  
End Sub
 
Upvote 0
I changed it to this:
rlst = rlst & ", " & Chr(39) & .ItemData(j) & Chr(39)
added a space after the comma. It still displays a receiver number twice, but works fine in my SQL.

I am still curious why it is grabbing a number twice though? Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

MCTampa
  • Solved
Mixing Number and Text Query Criteria from a Form
Replies
5
Views
420
MCTampa
MCTampa
C
  • Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
196
welshgasman
welshgasman
K
  • Question
Help repairing a Query Expression show records by a specified year
Replies
1
Views
200
Micron
M
K
  • Solved
Building email recipient list for multiple addresses
Replies
8
Views
354
Micron
M
S
  • Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
811
Micron
M

Forum statistics

Threads
1,215,007
Messages
6,122,670
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top