I got a small problem with getting a filter to work for my database.
When i test them seperatly it works but when i all add them together they stop working.

This is the code i use currently.
All of the fields in the database are text, except for the id which is autonumberic but not really used for the program.

The line thats right above the one thats turned on does work, but as you can see that one will only allow you to filter if you type the exact name.
So if i would search for Testname it would find it, but if i would type Test it would not find anything.

Does anybody see anything wrong?

 Private Sub BtnZoek_Click(sender As Object, e As EventArgs) Handles BtnZoek.Click
        Dim StrNaam As String
        Dim StrVoornaam As String
        Dim StrFamNr As String
        Dim StrArchKast As String
        Dim StrOpmerking As String


        StrNaam = TxtNaam.Text
        StrVoornaam = TxtVoornaam.Text
        StrFamNr = TxtFamNr.Text
        StrArchKast = TxtArchiefKast.Text
        StrOpmerking = TxtOpmerking.Text

        Zoeken("C:\FB.accdb", StrNaam, StrVoornaam, StrFamNr, StrArchKast, StrOpmerking)
    End Sub

    Public Sub Zoeken(ByVal DBase As String, ByVal Naam As String, ByVal Voornaam As String, ByVal FamNr As String, ByVal ArchKast As String, ByVal Opmerking As String)

        Dim sql As String
        Dim connetionString As String
        Dim connection As OleDbConnection
        Dim command As OleDbCommand
        Dim ds As New DataSet
        Dim oledbadapter As New OleDbDataAdapter

        connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & DBase
        'sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam + "%" & "' OR Voornaam = '" & Voornaam + "%" & "' OR FamilieNummer = '" & FamNr & "' OR ArchiefKast LIKE '" & ArchKast & "'"
        sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam + "%" & "' AND Voornaam LIKE '" & Voornaam + "%" & "' OR FamilieNummer = '" & FamNr & "' OR ArchiefKast LIKE '" & ArchKast & "'"

        'sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam + "%" & "' AND Voornaam LIKE '" & Voornaam + "%" & "'"
        'sql = "Select * from FamBestand WHERE FamilieNummer LIKE '" & FamNr & "'"
        'sql = "Select * from FamBestand WHERE ArchiefKast LIKE '" & ArchKast + "%" & "'"
        'sql = "Select * from  FamBestand WHERE Opmerking Like '" & Opmerking + "%" & "'"


        'sql = "Select * from FamBestand WHERE Naam LIKE '" & Naam & "' OR Voornaam LIKE '" & Voornaam & "' OR FamilieNummer = '" & FamNr & "' OR ArchiefKast LIKE '" & ArchKast & "'"
        connection = New OleDbConnection(connetionString)

        Try
            connection.Open()
            ds.Clear()
            command = New OleDbCommand(sql, connection)
            oledbadapter.SelectCommand = command
            oledbadapter.Fill(ds, "Create DataView")
            oledbadapter.Dispose()
            command.Dispose()
            connection.Close()
            FamBestandDataGridView.DataSource = ds.Tables(0).DefaultView
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    End Sub

Edited 3 Years Ago by Sevyt

Have you verified the logic of the select?

I see (1) AND (2) OR (3) OR (4)

If this statement gets qualified, you can have a maximum of 3 outcomes.

(1) and (2) are true
(3) is true
(4) is true

Is this correct?

Sorry just noticed i posted the wrong code.
What you said was correct, they were all supposed to be AND.

The problem was eventually fixed with a variable query.

This question has already been answered. Start a new discussion instead.