Access数据库模糊查询实现：VB.NET中检索包含指定字符串的记录

Got it, let's tweak your code to make the Description field support partial matches instead of strict exact matches. Here's what you need to do:

The Issue with Your Current Code #

Right now, using the = operator for the Description filter only pulls up entries that match your search text perfectly. For the "contains any occurrence" behavior you want (like finding all entries with "Syringe" anywhere in the Description), we need to use Access's LIKE operator paired with wildcards.

Updated Code #

Here's the revised btnSearch_Click method, with the Description case adjusted for fuzzy searches. I also added a fix to handle single quotes in the search input—this prevents syntax errors if someone searches for a term like "Syringe's Tip":

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    Dim stock As String
    stock = cmbSearch.Text
    
    ' Escape single quotes to avoid filter syntax errors
    Dim safeSearchText As String = txtSearch.Text.Replace("'", "''")
    
    Select Case stock
        Case "Stock Level"
            Me.Sheet1BindingSource.Filter = "Stock = '" & safeSearchText & "'"
        Case "Description"
            ' Use LIKE with * wildcards for partial matches (Access uses * instead of %)
            Me.Sheet1BindingSource.Filter = "Description LIKE '*" & safeSearchText & "*'"
        Case "Article"
            Me.Sheet1BindingSource.Filter = "Article = '" & safeSearchText & "'"
        Case "Merche Cat"
            Me.Sheet1BindingSource.Filter = "Merche Category = '" & safeSearchText & "'"
        Case "Merche Dis"
            Me.Sheet1BindingSource.Filter = "Merche Description = '" & safeSearchText & "'"
    End Select
End Sub

Key Changes Breakdown #

• LIKE Operator: Swapped = for LIKE—this tells Access to look for partial matches instead of exact ones.
• * Wildcards: Added * before and after the search text. This matches any string that includes your search term anywhere (so "Syringe" will catch "syringe 10ML", "ABL syringe", etc.). Note: Access uses * as the wildcard character (unlike some other SQL databases that use %).
• Single Quote Handling: The Replace("'", "''") ensures that if your search text has a single quote, it won't break the filter's syntax.

Optional: Extend to Other Fields #

If you want fuzzy search for other fields later, just apply the same pattern: replace = with LIKE and wrap the search text in * wildcards. For example, for the Article field:

Me.Sheet1BindingSource.Filter = "Article LIKE '*" & safeSearchText & "*'"

内容的提问来源于stack exchange，提问作者Nutella Crumpy