Solved Search multiple values in ListBox

[moldreams23]

Hello,

I am new to MS access & trying to achieve something like below, If user wants to search the record for multiple values in List box like EMP ID - 933950 ,445022, 902167. how we need to build the criteria? I am attaching the test file here for your reference.

When I put multiple values in the textbox then list box should show the result for all the three records. like below:

I am able to get this using IN operator of SQL query option but not sure how to use in the textbox, list box. Please help.

Thank you!

 

[Gasman]

You could do something along these lines?

Private Sub Command0_Click()
Dim strWhere As String, strNewSource As String
Dim strRowSource() As String
Me.List3.RowSource = Replace(Me.List3.RowSource, ";", "")
Debug.Print Me.List3.RowSource
strRowSource = Split(Me.List3.RowSource, " WHERE ")
Debug.Print strRowSource(0)
strNewSource = strRowSource(0) & " WHERE [EMP ID] IN (" & Me.txtSearch & ")"
Debug.Print strNewSource
Me.List3.RowSource = strNewSource
'Me.List3.Requery

I changed the textbox to txtSearch. Start giving yourself meaningful names, else 6 months down the road, it will mean nothing.
With my memory, that could be 6 hours.

Comment out/remove the debug.prints when you are happy it is working.

 

[moldreams23]

Hello Gasman,

Thank you so much for your prompt response. I have tried in the attached file but some how it is not working, can you please check if I am missing something...

Thanks
Anmol

 

[Gasman]

Works fine for me?

FWIW, here is some code to reset and clear the source for your Clear button.

Private Sub Command7_Click()
Dim strRowSource() As String
strRowSource = Split(Me.List3.RowSource, " WHERE ")
Debug.Print strRowSource(0)
Me.List3.RowSource = strRowSource(0)
Me.txtSearch = ""
End Sub

 

[arnelgp]

here test this one.

 

[moldreams23]

Yes, Hats off to you!! It is working now & understood well.

Thank you!!

 

[Gasman]

The

understood well

is more rewarding than the

It is working now

 

[moldreams23]

here test this one.

Hi

here test this one.

Hi arnelgp,

I am unable to search the record due to some reason in the production file. However when I put the EMP ID e.g. "276679" in double quotes it is working fine. I am sorry as not able to share the file here....Is there anything which I am missing? Please help.

thank you!!
Anmol...

 

[moke123]

However when I put the EMP ID e.g. "276679" in double quotes it is working fine.

Which indicates it is a string datatype and not numerical. You will need to delimit the text with quotes.

exchange this line

Me.List3.RowSource = conSQL & " where [emp id] in (" & srch & ");"

with this one

Me.List3.RowSource = conSQL & " where [emp id] in (""" & srch & """);"

I dont think it will work with multiple ID's entered

 

[Gasman]

Emp_ID was numeric when I worked on the DB?

 

[moke123]

I am unable to search the record due to some reason in the production file. However when I put the EMP ID e.g. "276679" in double quotes it is working fine.

The test DB was numerical but I suspect the production DB has it as text, thus it works with quotes.

If it is text then each element in the IN clause needs to be delimited, ie "22089', "44089","55912"

 

[Gasman]

The test DB was numerical but I suspect the production DB has it as text, thus it works with quotes.

If it is text then each element in the IN clause needs to be delimited, ie "22089', "44089","55912"

Aaaaarrrrgggghhhhhhh
Why do people change things, when there is no need?

Edit: In fact from the first pic posted Em_ID is right justified?

 

[arnelgp]

with the new code, you do not need to add single/double quote.
just type it normally, eg:

123454,66655

 

[smtazulislam]

with the new code, you do not need to add single/double quote.
just type it normally, eg:

123454,66655

wow. Its really nice searcher board. Many times I tried to do that.

 

[smtazulislam]

with the new code, you do not need to add single/double quote.
just type it normally, eg:

123454,66655

Here if I have [Emp ID] Field is AUTONUMBERS. then need any change?
CAUSE, autonumbers filed not working.

 

[arnelgp]

if the field is Numeric, like Autonumber field use the db in post #5.

 

[moldreams23]

with the new code, you do not need to add single/double quote.
just type it normally, eg:

123454,66655

It is working perfectly fine now... thank you so much!!

 

[moldreams23]

Aaaaarrrrgggghhhhhhh
Why do people change things, when there is no need?

Edit: In fact from the first pic posted Em_ID is right justified?

I am sorry I forgot about it previously...

 

[moldreams23]

I just want to thank you so much!! @arnelgp, @Gasman and @moke123 for your help on this.

 

[KitaYama]

if the field is Numeric, like Autonumber field use the db in post #5.

@arnelgp Just a suggestion
You could have checked for data type of the field to see if there's a need to add a prefix/suffix or not :

Function getSuffix(tbl As String, fld As String) As String
    Dim intFieldType As Integer
  
    intFieldType = CurrentDb.TableDefs(tbl).Fields(fld).Type
    Select Case intFieldType
        Case 2 'Byte
            getSuffix = ""
        Case 3 'Integer
            getSuffix = ""
        Case 4 'Long
            getSuffix = ""
        Case 6 'Single
            getSuffix = ""
        Case 7 'Double
            getSuffix = ""
        Case 8 'Date
            getSuffix = "#"
        Case 10 'String
            getSuffix = "'"
    End Select
End Function

And use it like :

      fld = "OrderNo"
      Suffix = getSuffix("tblOrders", fld)
      sql = "SELECT * FROM tblOrders WHERE " & fld & "=" & Suffix & fld & Suffix & """"

Note : Air Code, not tested. It may need some correction.