I have made my Search Form that will search through my tables, some how I have a syntax error for the following: year, Section and ID, these fields are not text I use numeric values so I think the search is not working for that reason, I am not sure how can I fix it though do I have do add val() or sth.
Code:
Private Sub Command8_Click()
On Error GoTo Command8_ClickError
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim strWhereCondition As String
Dim strSql As String
strWhereCondition = ""
strSql = "Select distinct Id From AcademicVideo Where True "
'================================'
If Not IsNull(ID) And Trim(ID) <> "" Then
strSql = strSql & " And [Id] = " & [ID]
End If
'================================'
If Not IsNull(Course) And Trim(Course) <> "" Then
If InStr(Course, "*") = 0 Then
strSql = strSql & " And [Course] = '" & [Course] & "'"
Else
strSql = strSql & " And [Course] like '" & [Course] & "'"
End If
End If
'================================'
If Not IsNull([Format]) And Trim([Format]) <> "" Then
If InStr([Format], "*") = 0 Then
strSql = strSql & " And [Format] = '" & [Format] & "'"
Else
strSql = strSql & " And [Format] like '" & [Format] & "'"
End If
End If
'================================'
If Not IsNull([Title]) And Trim([Title]) <> "" Then
If InStr([Title], "*") = 0 Then
strSql = strSql & " And [Title] = '" & [Title] & "'"
Else
strSql = strSql & " And [Title] like '" & [Title] & "'"
End If
End If
'================================'
If Not IsNull([Lecturer]) And Trim([Lecturer]) <> "" Then
If InStr([Lecturer], "*") = 0 Then
strSql = strSql & " And [Lecturer] = '" & [Lecturer] & "'"
Else
strSql = strSql & " And [Lecturer] like '" & [Lecturer] & "'"
End If
End If
'================================'
' If Not IsNull([Section]) And Trim([Section]) <> "" Then
' If InStr([Section], "*") = 0 Then
' strSql = strSql & " And [Section] = " & [Section] & "'"
' Else
' strSql = strSql & " And [Section] like " & [Section] & "'"
' End If
' End If
'================================'
If Not IsNull([Semester]) And Trim([Semester]) <> "" Then
If InStr([Semester], "*") = 0 Then
strSql = strSql & " And [Semester] = '" & [Semester] & "'"
Else
strSql = strSql & " And [Semester] like '" & [Semester] & "'"
End If
End If
'================================'
If Not IsNull([Year]) And Trim([Year]) <> "" Then
If InStr([Year], "*") = 0 Then
strSql = strSql & " And [Year] = " & [Year] & "'"
Else
strSql = strSql & " And [Year] like " & [Year] & "'"
End If
End If
'================================'
If Not IsNull([Description]) And Trim([Description]) <> "" Then
If InStr([Description], "*") = 0 Then
strSql = strSql & " And [Description] = '" & [Description] & "'"
Else
strSql = strSql & " And [Description] like '" & [Description] & "'"
End If
End If
'================================'
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
' If (rs2.RecordCount = Null) Then
' MsgBox "Enter A valid Record"
If (rs.RecordCount = 0) Then
MsgBox "Could Not found "
Else
strWhereCondition = "[Id] In (" & rs!ID
Do While Not rs.EOF
strWhereCondition = strWhereCondition & ", " & rs!ID
rs.MoveNext
Loop
strWhereCondition = strWhereCondition & ")"
End If
rs.Close
If strWhereCondition <> "" Then
DoCmd.OpenForm "ACVideo", acNormal, , strWhereCondition
DoCmd.Close acForm, "Search AcVideo"
End If
Exit Sub
Command8_ClickError:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub