Solved Using like with multiple criteria in the same field (1 Viewer)

[Jslo11]

I have a table with the field stock and contact. There are rows that have:

# /stock / contact
1. IBM / sally
2. IBM / joe
3. CSCO / terry
4. AMZN / joe
I want to be able to prompt the user to pick whatever stocks they want to look for, and show only those names

I.e. search - like * AMZN,IBM, HD

please help me figure out how to type in AMZN , IBM, and HD and see the rows 1.2.4. Effectively selecting based on user number of tickers

 

[Micron]

If you're going to input the actual names, what you need isn't LIKE it's IN except -
users should not be playing in tables, so what is the method that a user is providing the values? That is important info.
You could do this with a multi select listbox so that the values returned depend on what they've selected in it.

 

[theDBguy]

Hi. Welcome to AWF!

Here's another possible approach. Hope it helps...

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with p...

thedbguy.blogspot.com

 

[Micron]

Whoops. I usually suggest that new-comers don't post questions in the introductory forum. I missed that.

 

[strive4peace]

I made a form for you. After you enter the stocks separated with a comma, press the TAB key -- or click somewhere else

Here is the code that processes the form filter on the AfterUpdate event:

Private Sub fltrStock_AfterUpdate()
'200414 strive4peace

   'dimension variables
   Dim sFilter As String _
      , i As Integer
   Dim aValue() As String

   With Me.fltrStock
      If IsNull(.Value) Then
         'turn the form filter off
         Me.FilterOn = False
      Else
         'split comma delimited value into array of values
         aValue = Split(.Value, ",")
         For i = LBound(aValue) To UBound(aValue)
            'surround each value with double quotes
            'Trim to remove extra spaces
            aValue(i) = """" & Trim(aValue(i)) & """"
         Next i
         'join the values. Use the IN clause for the form filter
         sFilter = "stock IN (" & Join(aValue, ",") & ")"
         Me.Filter = sFilter
         'turn the form filter on
         Me.FilterOn = True
      End If
   End With
End Sub

database is attached

 

[strive4peace]

Welcome, Jslo11! I reported this to ask if this question can be moved to the Forms Forum. the Introduce Yourself forum is for saying Hi to everyone and telling us a little bit about yourself.

if you have any questions about the code, ask! We want you to learn

 

[isladogs]

Moved to Forms subforum as requested

 

[strive4peace]

thank you, Colin!

 

[Jslo11]

I made a form for you. After you enter the stocks separated with a comma, press the TAB key -- or click somewhere else

View attachment 80925

Here is the code that processes the form filter on the AfterUpdate event:

Private Sub fltrStock_AfterUpdate()
'200414 strive4peace

   'dimension variables
   Dim sFilter As String _
      , i As Integer
   Dim aValue() As String

   With Me.fltrStock
      If IsNull(.Value) Then
         'turn the form filter off
         Me.FilterOn = False
      Else
         'split comma delimited value into array of values
         aValue = Split(.Value, ",")
         For i = LBound(aValue) To UBound(aValue)
            'surround each value with double quotes
            'Trim to remove extra spaces
            aValue(i) = """" & Trim(aValue(i)) & """"
         Next i
         'join the values. Use the IN clause for the form filter
         sFilter = "stock IN (" & Join(aValue, ",") & ")"
         Me.Filter = sFilter
         'turn the form filter on
         Me.FilterOn = True
      End If
   End With
End Sub

database is attached

thank you, Colin!

thank you so much for doing this.
A few questions.

THIS IS AMAZING.

how should shows: contact / matches in one field
I.e. Joe / AMZN, IBM

I may also want to look at two tables - this one (StockContact.tbl) and (StockInterestList.tbl) that have stocks w a contact. Then display thein one form, search the same way, and see it like this:

Joe /AMZN, IBM (StockContact.tbl), AMZN (StockInterestList.tbl

I’m new to the programming. So so helpful

 

[Jslo11]

I made a form for you. After you enter the stocks separated with a comma, press the TAB key -- or click somewhere else

View attachment 80925

Here is the code that processes the form filter on the AfterUpdate event:

Private Sub fltrStock_AfterUpdate()
'200414 strive4peace

   'dimension variables
   Dim sFilter As String _
      , i As Integer
   Dim aValue() As String

   With Me.fltrStock
      If IsNull(.Value) Then
         'turn the form filter off
         Me.FilterOn = False
      Else
         'split comma delimited value into array of values
         aValue = Split(.Value, ",")
         For i = LBound(aValue) To UBound(aValue)
            'surround each value with double quotes
            'Trim to remove extra spaces
            aValue(i) = """" & Trim(aValue(i)) & """"
         Next i
         'join the values. Use the IN clause for the form filter
         sFilter = "stock IN (" & Join(aValue, ",") & ")"
         Me.Filter = sFilter
         'turn the form filter on
         Me.FilterOn = True
      End If
   End With
End Sub

database is attached

Thank you again - this is so much better than how I was trying to do it. I really appreciate your time and help.

 

[Jslo11]

Welcome, Jslo11! I reported this to ask if this question can be moved to the Forms Forum. the Introduce Yourself forum is for saying Hi to everyone and telling us a little bit about yourself.

if you have any questions about the code, ask! We want you to learn

Thank you.
I’m just a casual user of access, trying to learn to be better.

 

[strive4peace]

hi Jslo11,

you're welcome! Better would be to have one table for

1. Stocks, with an AutoNumber StockID for the primary key (PK).
2. Contacts table with CID AutoNumber PK, ContactName*, and whatever else you want to keep track of for the contact.
3. StockContacts with 2 Long Integer foreign keys (FKs) -- StockID and CID, with a unique index so that combination isn't duplicated.. Also have an Ordr field (order is a reserved word)m Integer, for the priority of the contact, or the one to list if you only list 1 of them. I like to make Ordr=0 for the main record.

Make sure the default value for all FKs is blank -- Access 'kindly' adds a 0 for all number data types, but you don't want a default value for a FK. It would also have an AutoNumber PK

Make another sample database and we can use that for code.

Why do you want criteria in a query instead of filtering a form or report?

 

[strive4peace]

* ps, Jslo11

on contacts ... don't know how much information you keep track of about them, but perhaps this free contact management database would be helpful:
msAccessGurus.com/tool/MyContacts.htm