Problem with search form (1 Viewer)

[poh99]

Hi, I have a search form with a subform. My search input is at the top while my result will show at the subform. However, most of my search works except for 1 or 2 records will always not found in the search. The information were in my table just now found when search. Any expert can guide me on this?

 

[bob fitz]

We will need much more detail than you have given to be able any detailed advice.
Perhaps you could post a copy of the db with some instructions on what to search for to demonstrate your problem.

 

[MajP]

If your search form creates a where clause or filter then post the results of that criteria string. Then post the data types of the fields referenced in the search.

 

[poh99]

Sorry, earlier miss out on my code

Private Sub btnsearch_Click()

    Dim strText As String
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM SearchStudent "
    strText = "1 "

    If Not IsNull(Me.[Name]) Then
        strText = strText & " AND [Name] like '*" & Me.[Name] & "*' "
    End If
   
    If Not IsNull(Me.[StudentID]) Then
        strText = strText & " AND [StudentID] = '" & Me.[StudentID] & "'"
    End If
   
    If Not IsNull(Me.[Contact]) Then
        strText = strText & " AND [Contact] Like '*" & Me.[Contact] & "*' "
    End If
   
    If Not IsNull(Me.[Email]) Then
        strText = strText & " AND [Email] = '" & Me.[Email] & "'"
    End If
   
   
    If strText <> "1 " Then
        strSQL = strSQL & " WHERE " & strText
        Set rst = CurrentDb.OpenRecordset(strSQL)
        If Not rst.EOF Then
            Set SearchSubForm_1.Form.Recordset = rst
        Else
            MsgBox "Nothing Found"
        End If
    Else
        btnclear_Click
    End If

End Sub

 

[MajP]

put a
debug.print StrSql before the openrecordset so we can see what the strSql resolves to. Do the case where the records are not properly returned.
You will probably be able to see the problem yourself.

 

[Pat Hartman]

It is possible that your search fields contain a ZLS (Zero Length String) and is not actually null. One way to get around that problem is to concatenate a ZLS to the control and then check for ZLS rather than null

If Me. & "" <> "" Then

Otherwise, the issue is a data problem. The records that don't show have unexpected values in them.

PS - in addition to not using special characters or embedded spaces, you should avoid reserved words like the plague. That goes double for "Name" and "Date". EVERY object has a Name property and you will be quite unpleasently surprised at what Me.[Name] returns.
[ATTACH type="full"]96876[/ATTACH]

Access warns you but you can blow by the error if you think you know better - but you really don't.

[ATTACH type="full"]96875[/ATTACH]

Rename any field named Name or Date before it comes back to bite you.