1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
|
Private Sub Btn_Search_Click()
Dim myMedia()
Dim myQuery()
Dim DateCompare
Dim MediaSearch As Boolean
Dim DateSearch As Boolean
Dim j
myMedia = Array(Me!Cocher_Paper, Me!Cocher_Map, Me!Cocher_Audio, Me!Cocher_Video, Me!Cocher_Picture)
j = 0
' SETTING UP DATE SEARCH CRITERIA
If Me!TxtDate <> "" And Me!RangeDate = "Before" Then
DateSearch = True
DateCompare = "<="
ElseIf Me!TxtDate <> "" And Me!RangeDate = "On" Then
DateSearch = True
DateCompare = "="
ElseIf Me!TxtDate <> "" And Me!RangeDate = "After" Then
DateSearch = True
DateCompare = ">="
Else
DateSearch = False
End If
' CHECKING MEDIA IS SELECTED FOR THE SEARCH
If Me!Cocher_Paper = Flase And Me!Cocher_Map = False And Me!Cocher_Audio = False And Me!Cocher_Video = False And Me!Cocher_Picture = False Then
MediaSearch = False
Else
MediaSearch = True
End If
If Me!TxtSearch <> "" And MediaSearch = True Then
' IF SEARCH IN TITLE
If Me!Cadre_Options = 1 Then
For i = 0 To UBound(myMedia)
If myMedia(i) Then
ReDim Preserve myQuery(j)
If DateSearch Then
myQuery(j) = "SELECT [ID_Document], [Title], [Author], [CreationDate], [Digitized], [TrailMark], [Media] FROM T_Documents, T_Types WHERE [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [CreationDate] " & DateCompare & " #" & Format(Me!TxtDate, "yyyy-mm-dd") & "# AND [Title] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*'"
Else
myQuery(j) = "SELECT [ID_Document], [Title], [Author], [CreationDate], [Digitized], [TrailMark], [Media] FROM T_Documents, T_Types WHERE [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [Title] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*'"
End If
j = j + 1
End If
Next i
' IF SEARCH IN KEYWORD
ElseIf Me!Cadre_Options = 2 Then
For i = 0 To UBound(myMedia)
If myMedia(i) Then
ReDim Preserve myQuery(j)
If DateSearch Then
myQuery(j) = "SELECT DISTINCT [ID_Document], [Title], [Author], [CreationDate], [Digitized], [TrailMark], [Media] FROM T_Documents, T_Types, T_Junction, T_Keywords WHERE [ID_Document] = [Document_ID] AND [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [CreationDate] " & DateCompare & " #" & Format(Me!TxtDate, "yyyy-mm-dd") & "# AND [Keyword_ID] = [ID_Keyword] AND [Word] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*'"
Else
myQuery(j) = "SELECT DISTINCT [ID_Document], [Title], [Author], [CreationDate], [Digitized], [TrailMark], [Media] FROM T_Documents, T_Types, T_Junction, T_Keywords WHERE [ID_Document] = [Document_ID] AND [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [Keyword_ID] = [ID_Keyword] AND [Word] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*'"
End If
j = j + 1
End If
Next i
' IF SEARCH IN TITLE & KEYWORD
ElseIf Me!Cadre_Options = 3 Then
For i = 0 To UBound(myMedia)
If myMedia(i) Then
ReDim Preserve myQuery(j)
If DateSearch Then
myQuery(j) = "SELECT DISTINCT [ID_Document], [Title], [Author], [CreationDate], [Digitized], [TrailMark], [Media] FROM T_Documents, T_Types, T_Junction, T_Keywords WHERE ([ID_Document] = [Document_ID] AND [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [CreationDate] " & DateCompare & " #" & Format(Me!TxtDate, "yyyy-mm-dd") & "# AND [Keyword_ID] = [ID_Keyword] AND [Title] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*') OR ([ID_Document] = [Document_ID] AND [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [CreationDate] " & DateCompare & " #" & Format(Me!TxtDate, "yyyy-mm-dd") & "# AND [Keyword_ID] = [ID_Keyword] AND [Word] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*')"
Else
myQuery(j) = "SELECT DISTINCT [ID_Document], [Title], [Author], [CreationDate], [Digitized], [TrailMark], [Media] FROM T_Documents, T_Types, T_Junction, T_Keywords WHERE ([ID_Document] = [Document_ID] AND [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [Keyword_ID] = [ID_Keyword] AND [Title] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*') OR ([ID_Document] = [Document_ID] AND [TypeID] = [ID_Type] AND [TypeID] = " & i + 1 & " AND [Keyword_ID] = [ID_Keyword] AND [Word] like '*" & Me!TxtSearch & "*' AND [Author] like '*" & Me!TxtAuthor & "*')"
End If
j = j + 1
End If
Next i
End If
' UNION OF QUERIES FOR EACH MEDIA SEARCHED FOR
For i = 0 To UBound(myQuery)
If i = 0 Then
StrRowSource = myQuery(i)
Else
StrRowSource = StrRowSource & " UNION " & myQuery(i)
End If
Next i
Me!ListSearchResults.RowSource = StrRowSource
Else
MsgBox "No text in Search box OR No Media Support selected...", vbOKOnly, "Warning"
End If
End Sub |
Partager