Results 1 to 7 of 7

Thread: Need some help

  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    2

    Question Unanswered: Need some help

    I'm trying to search a database using the following:

    Me.Recordset.FindFirst "[ID Number] Like '" & SearchString & "'"

    The [ID Number] field is a string of eight characters (usually numbers) and often start with one or more zeros.

    The above which works find unless the ID Number starts with a zero, in which case I get no matches regardless of whether I use wildcards in the SearchString.

    So if the database contained a record with [ID Number] being "12345678" and I searched for "12345678" I would get a match.

    If the database contained a record with [ID Number] being "0001234" and I searched for "00001234", or "*1234" or even "*1234*" I wouldn't get any matches! I'm confused - can anyone out there help!?

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Hmmmm

    I wrote this to solve it, but there should be a better way.


    Dim Rst As New ADODB.Recordset
    Dim Cnn As New ADODB.Connection

    Set Cnn = CurrentProject.Connection

    Rst.Open "Tbl_Your_Table", Cnn, adOpenKeyset, adLockOptimistic

    Rst.MoveFirst

    Do Until Rst.EOF
    If InStr(1, Rst.Fields("Field_Name"), "find_this_Text", vbTextCompare) Then
    Debug.Print Rst.Fields("Field_Name")
    End If
    Rst.MoveNext
    Loop
    Rst.Close
    Cnn.Close


    This should debug.print every match of whatever text you type in..

    Hope this helps till a better solution arrives...



    Ken

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Use the "%" sign instead of the asterisk ...

  4. #4
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    See a better solution arrived

    Cheers M Owen !!

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    This is that difference between the QBE which uses the asterisk for wildcards and ADO/DAO/SQL where the percent sign needs to be use for wildcards ...

    I will say one thing tho as a criticism: This particular question has been asked numerous times and answered the same number of times ... A simple search of "wildcard" would have yielded the answer and then Ken and I wouldn't have needed to answer this once again ...

  6. #6
    Join Date
    Mar 2004
    Location
    UK
    Posts
    2
    Originally posted by M Owen
    This is that difference between the QBE which uses the asterisk for wildcards and ADO/DAO/SQL where the percent sign needs to be use for wildcards ...

    I will say one thing tho as a criticism: This particular question has been asked numerous times and answered the same number of times ... A simple search of "wildcard" would have yielded the answer and then Ken and I wouldn't have needed to answer this once again ...
    Thanks for that guys. I did actually do a search for Wildcard and couldn't find anything that solved my problem. I have tried using the % instead of * in the searchstring and I still get no matches.

    I can't figure this out because I can see the files it should be finding. It's almost as if it removes any zeros preceeding a number, though I assumed that since the number is actually stored as a string, and not an integer, it wouldn't do this.

    Any more ideas?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Tommy O
    Thanks for that guys. I did actually do a search for Wildcard and couldn't find anything that solved my problem. I have tried using the % instead of * in the searchstring and I still get no matches.

    I can't figure this out because I can see the files it should be finding. It's almost as if it removes any zeros preceeding a number, though I assumed that since the number is actually stored as a string, and not an integer, it wouldn't do this.

    Any more ideas?
    Let's see your query ... Just as an aside, here's one of my filter constuction functions using LIKE:

    Code:
    Function ConstructFilter() As String
        Dim FilterString As String
        Dim HaveFrom As Boolean, HaveThru As Boolean
        
        FilterString = "( "
        If SearchOnGroup.Value <> 0 Then
            Select Case SearchOnGroup.Value
                Case 1  ' Project Title
                    FilterString = FilterString & "[Work Orders II].[Project Title]"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 2  ' Work Order
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber"
                    Select Case MatchGroupBox.Value
                        Case 1  ' Exact
                            FilterString = FilterString & "='" & SearchTxt.Value & "'"
                        Case 2  ' Partial
                            FilterString = FilterString & " LIKE '%" & SearchTxt.Value & "%'"
                    End Select
                Case 3  ' Brand
                    FilterString = FilterString & "[Work Orders II].WorkOrderNumber LIKE '%" & BrandComboBox.Value & "%'"
                Case 4  ' Requestor
                    FilterString = FilterString & "[Work Orders II].OwnerID='" & OwnerComboBox.Value & "'"
                Case 5  ' Customer
                    FilterString = FilterString & "CustomerContacts.ContactID=" & CustomerComboBox.Value
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        If OpenGroup.Value <> 0 Then
            If SearchOnGroup.Value <> 0 Then FilterString = FilterString & " AND ( "
            Select Case OpenGroup.Value
                Case 1  ' Open
                    FilterString = FilterString & "[Work Orders II].Closed=False"
                Case 2  ' Closed
                    FilterString = FilterString & "[Work Orders II].Closed=True"
                Case Else
                    MsgBox "Garbage"
            End Select
            FilterString = FilterString & " )"
        End If
        
        HaveFrom = False
        HaveThru = False
        If FromTxt.Value & "" <> "" Then HaveFrom = True
        If ThruTxt.Value & "" <> "" Then HaveThru = True
        If HaveFrom Or HaveThru Then
            FilterString = FilterString & " AND ( [Work Orders II]."
            If OpenGroup.Value = 2 Then
                FilterString = FilterString & "ClosingDate"
            Else
                FilterString = FilterString & "IssueDate"
            End If
        End If
        If HaveFrom And HaveThru Then
        FilterString = FilterString & " BETWEEN #" & FromTxt.Value & "# AND #" & ThruTxt.Value & "#"
        Else
            If HaveFrom Then FilterString = FilterString & "=#" & FromTxt.Value & "#"
            If HaveThru Then FilterString = FilterString & "=#" & ThruTxt.Value & "#"
        End If
        If HaveFrom Or HaveThru Then FilterString = FilterString & " )"
        
        ConstructFilter = FilterString
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •