Search, Text VS Numeric.

romio

Registered User.
Local time
Today, 00:09
Joined
Apr 20, 2005
Messages
68
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
 
For the numeric field using =, remove the ending single-quote.
But, for the part using LIKE, add an opening single-quote.

Hence the code for the numeric Year field is:-
Code:
    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


SECTION is a form property. It mustn't be used as a text box name. Instead, you can use something like txtSection
.
 
Last edited:
Thanks Jon its Fixed now.
 

Users who are viewing this thread

Back
Top Bottom