Wildcard search not working. [RESOLVED]

[hscott]

Hi,
I have created a form to search a database using wildcard searches.
In my database I have a column named Asbestos Type. In this column I have three different values; 'Chrysotile', 'Amosite' and 'Chrysotile and Amosite'.
When I search for Chrysotile, both 'Chrysotile' and 'Chrysotile and Amosite' are returned. When I search for Amosite only 'Amosite' is returned. Does anyone know what could be causing this?
Thanks in advance for the help.

Option Explicit
Private rsSearch As ADODB.Recordset
Private cn As ADODB.Connection
 
Private Sub Form5_Load()
LoadTheGrid "SELECT * FROM Asbestos"
End Sub
 
Private Function GetRecordset(ByVal strSQL As String) As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Documents and Settings\Harry Scott\My Documents\Homework\Computing\Asbestos Register\Database.mdb"
cn.CursorLocation = adUseClient
cn.Open
Set rsSearch = New ADODB.Recordset
rsSearch.CursorLocation = adUseClient
Set rsSearch = cn.Execute(strSQL)
Set rsSearch.ActiveConnection = Nothing
cn.Close
Set GetRecordset = rsSearch
End Function
 
Private Sub LoadTheGrid(ByVal strSQL As String)
Dim rs As ADODB.Recordset
Dim iCol As Integer, iRow As Integer
Dim fld As ADODB.Field
Set rs = GetRecordset(strSQL)
iRow = 0
Me.MSFlexGrid1.Rows = rs.RecordCount
If rs.RecordCount = 0 Then
MsgBox ("No records meet your search query.")
End If
Me.MSFlexGrid1.Cols = rs.Fields.Count
Do Until rs.EOF
iCol = 0
For Each fld In rs.Fields
Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
iCol = iCol + 1
Next fld
iRow = iRow + 1
rs.MoveNext
Loop
End Sub
 
Private Sub CmdBack_Click()
frmEditAsbestos.Show
Form5.Hide
End Sub
 
Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
strSQL = "SELECT * FROM Asbestos"
If Text1.Text <> vbNullString Then
    strWhere = strWhere & " AND [Sample Number] LIKE """ & Text1.Text & "%"""
End If
If Text2.Text <> vbNullString Then
    strWhere = strWhere & " AND [Unit Number] LIKE """ & Text2.Text & "%"""
End If
If Text3.Text <> vbNullString Then
    strWhere = strWhere & " AND [Product Type] LIKE """ & Text3.Text & "%"""
End If
If Text4.Text <> vbNullString Then
    strWhere = strWhere & " AND [Asbestos Type] LIKE """ & Text4.Text & "%"""
End If
If Text5.Text <> vbNullString Then
    strWhere = strWhere & " AND [Location In Unit] LIKE """ & Text5.Text & "%"""
End If
If Text6.Text <> vbNullString Then
    strWhere = strWhere & " AND [PBC Job Number] LIKE """ & Text6.Text & "%"""
End If
If Text7.Text <> vbNullString Then
    strWhere = strWhere & " AND [Work Done] LIKE """ & Text7.Text & "%"""
End If
If Text8.Text <> vbNullString Then
    strWhere = strWhere & " AND [Date Of Completion] LIKE """ & Text8.Text & "%"""
End If
If Text9.Text <> vbNullString Then
    strWhere = strWhere & " AND [Air Test Certificate Number] LIKE """ & Text9.Text & "%"""
End If
If Text10.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Material] LIKE """ & Text10.Text & "%"""
End If
If Text11.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Priority] LIKE """ & Text11.Text & "%"""
End If
If Text12.Text <> vbNullString Then
    strWhere = strWhere & " AND [Assessment Score - Total] LIKE """ & Text12.Text & "%"""
End If
If strWhere <> vbNullString Then
    strSQL = strSQL & " WHERE " & Mid$(strWhere, 5)
End If
LoadTheGrid (strSQL)
End Sub
 
Private Sub Form5_Unload(Cancel As Integer)
rsSearch.Close
cn.Close
Set rsSearch = Nothing
Set cn = Nothing
End Sub