2427 You entered an expression that has no value

Question

I have a function to split up my report into separate PDF files based on a field id:

Public Function PrintList() As String

    Dim MyPath As String
    Dim MyFilename As String
    Dim myId As String
    Dim filter As String
    MyPath = "C:\reports\"

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT id FROM table")

    'Loop through the set
    Do While Not rs.EOF

        If Not IsEmpty(rs!id) And Not IsNull(rs!id) Then
            myId = rs!id
        Else
            myId = ""
        End If

        filter = "id = '" & myId & "'"

        'Open report preview and auto-save it as a PDF
        DoCmd.OpenReport "myReport", acViewPreview, , filter

        MyFilename = "N" & myId & ".pdf"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False

        'Close the previewed report
        DoCmd.Close acReport, "myReport"

        rs.MoveNext
    Loop

End Function

However, while running, I would get this error/warning:

2427 You entered an expression that has no value.

This happens when the id is null. I don't even know if this is an error or a warning. After I click Ok, the function continue to runs till the end without any problem, and the output file would be named N.pdf.

Since this message box is not like the usual error message box, and since it doesn't give me the option to debug or end the execution, I guess it is not an error?

I have tried to suppress warning using DoCmd.SetWarnings False but it doesn't work.

How can I fix this or at least suppress it so that it doesn't show up?

enter image description here

RBarryYoung RBarryYoung · Accepted Answer · 2014-08-27T15:41:46

There's no short-circuiting in VBA, so try changing this:

    If Not IsEmpty(rs!id) And Not IsNull(rs!id) Then
        myId = rs!id
    Else
        myId = ""
    End If

to this:

If Not rs!Id Is Nothing Then
    If Not IsNull(rs!id) Then
        If Not IsEmpty(rs!id) Then
            myId = rs!id
        Else
            myId = ""
        End If
    Else
        myId = ""
    End If
Else
    myId = ""
End IF

2427 You entered an expression that has no value

1 Answers