Search form that finds numeric with commas

[nikademous]

Hello, I needed to add to my search form the ability to search from a multi select list box for data in a numeric field (RuleOfConductNum) from my table (tbl_ContactRecord). The data in the field is saved from a multi list box with a comma between each number such as 2,6,3 or 7,11.

I want to be able to search from my multi listbox (lstFindContactNum). So if I selected 2 and 3 it would bring up the records that has a 2 and three in the (RuleOfConductNum) field. Below is the already in place search that I use.

To call the function I use:

Me.Form.RecordSource = "SELECT * FROM qry_SearchEntries " & BuildFilter
    Me.Requery

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim intIndex As Integer

    varWhere = Null
    
    If Not IsNull(Me.cboEmployeeName) And Me.cboEmployeeName <> 0 Then
'    If Not IsNull(Me.cboEmployeeName) Then
'      varWhere = varWhere & "([EmployeeName] = """ & Me.cboEmployeeName & """) AND "
        varWhere = varWhere & "([EmployeeID] = " & Me.cboEmployeeName & ") AND "
    End If


    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtPhraseContains) Then
        varWhere = varWhere & "([ReasonForContact] Like ""*" & Me.txtPhraseContains & "*"") AND "
    End If
    
    If Not IsNull(Me.cboYear) Then
        varWhere = varWhere & "Year([DateOfIncident]) = " & Me.cboYear & " AND "
    End If
 
        
    If Not IsNull(Me.optContactGroup) Then
        If Me.optContactGroup.Value = 1 Then
        varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        Else
        varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        End If
    End If
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
'   Debug.Print varWhere

End Function

 

[Andrzejek]

Looks like you need to go down your listbox, collect the numbers, and have something like:

...
varWhere = varWhere & "(RuleOfConductNum LIKE '*[blue]2[/blue]*' OR RuleOfConductNum LIKE '*[blue]3[/blue]*')"
...

and so on.

But this way, if you select 2 and 3, your search will also return 12, 35, and 625 :-(

BTW - Why are you using Variants instead of Strings?

---- Andy

There is a great need for a sarcasm font.

 

[nikademous]

I tried like but a different way and It didn't find anything more than 1 number.

varWhere = varWhere & "([RuleOfConductNum] Like ""*" & Me.lstFindContactNum & "*"") AND "

 

[Andrzejek]

Did you go down your "multi listbox (lstFindContactNum)" to pick all selected entries?
Here is some information about it.

---- Andy

There is a great need for a sarcasm font.

 

[Darrylles]

Can you post your "Me.Form.RecordSource" data content, or a subset of it?

p.s. If you are using the camel case naming convention for object naming, then underscore is completely pointless: "tbl_ContactRecord".
Either use camel case or use underscore - don't use both unless 2 capitals collide. ;-)

ATB,

Darrylle

 

[nikademous]

Attached is a portion of the database with some test data. The Switchboard form has the search on it that I want to add a multiselect listbox to find multi results from the field (RuleOfContactNum) in the table.