Syntax Error

[klwu]

The following is my piece of code for a search form, however it is not working, sometimes it comes out syntax error, sometimes it comes out OpenForm has been cancelled. Please someone guide me on this. Thanks a bunch!

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim sSql As String
    
    sSql = "1 = 1"
    
    If Not IsNull(cmbComRef) And cmbComRef <> "" Then
        sSql = sSql & " AND ComRef LIKE '*" & cmbComRef & "*'"
    End If
    If Not IsNull(txtRefNo) And txtRefNo <> "" Then
        sSql = sSql & " AND RefNo = " & txtRefNo
    End If
    If Not IsNull(txtName) And txtName <> "" Then
        sSql = sSql & " AND Name = " & txtName
    End If
    If Not IsNull(txtIPAdd) And txtIPAdd <> "" Then
        sSql = sSql & " AND IP = " & txtIPAdd
    End If
    If Not IsNull(txtPortNo) And txtPortNo <> "" Then
        sSql = sSql & " AND PortNo = " & txtPortNo
    End If
    If Not IsNull(txtNetworkID) And txtNetworkID <> "" Then
        sSql = sSql & " AND NetworkID = " & txtNetworkID
    End If
    
    DoCmd.OpenForm "SearchList", , , sSql
    
Exit_cmdSearch_Click:
    Exit Sub

Err_cmdSearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearch_Click
    
End Sub

 

[WayneRyan]

klwu,

Try this:

If Nz(txtPortNo, "") <> "" Then
        sSql = sSql & " AND PortNo = " & txtPortNo
End If

Wayne

 

[klwu]

Thanks Wayne, but unfortunately, the code is not working as well.
Anyway, what does the 'Nz' mean?

 

[john471]

Nz() is a function that converts Null into a Zero length string (i.e."") optionally, it can substitute any other supplied parameter for null when encoutnered, e.g.s:-

NZ(null,0) would return 0
NZ(55,0) would return 55
NZ(55,"hello") would return 55
and
NZ(null,"hello") would return hello.

Check out the sql that your code generates. Do any of the other items require enclosure in quotes (e.g. name) like ...

If Not NZ(txtName) = vbnullstring Then
sSql = sSql & " AND Name = '" & txtName & "'"
End If

???

Good Luck.