Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Search form that finds numeric with commas

Search form that finds numeric with commas

Search form that finds numeric with commas

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:

CODE --> vba

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

CODE --> vba

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 "
        varWhere = varWhere & "([Contact] = " & Me.optContactGroup & ") AND "
        End If
    End If
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
        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 

RE: Search form that finds numeric with commas

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


varWhere = varWhere & "(RuleOfConductNum LIKE '*2*' OR RuleOfConductNum LIKE '*3*')"
and so on.

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

BTW - Why are you using Variants instead of Strings?

---- Andy

There is a great need for a sarcasm font.

RE: Search form that finds numeric with commas

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

CODE --> vba

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

RE: Search form that finds numeric with commas

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.

RE: Search form that finds numeric with commas

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. winky smile



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close