Filter by 2 keywords

[nielsdl]

Hi,

I want to filter a form based on 2 keyword. It wordks for the first keyword (see code), but how do i get this worked for the second one?

The user selects the keyword from an automatic list in the form. But sometimes keyword A will be selected as second one because it's less important then the first keyword. So by filtering on keyword A i want to filter record 1, 2 and 4.

Record Keyword1 Keyword2
1 A B
2 A C
3 B D
4 B A

Private Sub Selection_open_Click()
On Error GoTo Err_Selection_open_Click

    If Not Me.List_keywords & "" = "" Then
        DoCmd.OpenForm "F_Fillinform", , , "[Keyword1]='" & Me.List_keywords & "'"

    Else
        DoCmd.OpenForm "F_Fillinform"
   End If
   End If
   Exit Sub
    
Err_Selection_open_Click:
    MsgBox Err.Description
    
End Sub

 

[arnelgp]

Private Sub Selection_open_Click()
DIM strCriteria As String
On Error GoTo Err_Selection_open_Click

    If Me.List_keywords & "" <> "" then
         strCriteria = "[KeyWord1] = '" & Me.List_Keywords & "'"
    End If
    If Me.List_Keywords2 & "" <> "" then
         If strCriteria <> "" Then
              strCriteria = strCriteria & " And [Keyword2] = '" & Me.List_Keywords2 & "'"
         Else
              strCriteria = "[Keyword2] = '" & Me.List_Keywords2 & "'"
         End If
    End If
    If strCriteria <> "" Then
        DoCmd.OpenForm "F_Fillinform", , , strCriteria

    Else
        DoCmd.OpenForm "F_Fillinform"
   End If
   End If
   Exit Sub
    
Err_Selection_open_Click:
    MsgBox Err.Description
    
End Sub

 

[pbaldy]

In my opinion the design is not normalized. There should be a single keyword field, so you'd have 8 records for your example. Filtering would then be easy, and you avoid the problem of which field the keyword is in.