NZ Invalid Use of Null

Question

I have a form that opens with on OnOpen event. Part of the event contains this code:

nid = Val(DMax("id2", "claims", "yr =" & yr) + 1)
nid = Val(Nz([nid], 1))

But, I get an "Invalid Use of Null" error when it runs. Any ideas?

EDIT BELOW Here is the full code:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim lngID As Long
Dim rs As Object

Set rs = Me.Recordset.Clone
lngID = Val(Me.OpenArgs)
rs.FindFirst "[ID] = " & lngID
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
Me.LOGID.SetFocus

'Display Suggested ID Numbers
Dim yr As Long
Dim nid As Long

yr = Format(Date, "yy")
nid = Val(DMax("id2", "claims", "yr =" & yr) + 1)
nid = Val(Nz([nid], 1))
Me.SugID = yr & "-" & nid

End Sub

Answer 1

Double-check the value returned by the DMax expression. If no rows match the criteria option ("yr =" & yr), DMax will return Null. Or if id2 is Null in all rows which match the criteria, DMax will return Null.

If the DMax expression returns Null, the Val() expression is equivalent to this:

Val(Null + 1)

But Null + 1 yields Null, so that is the same as asking for Val(Null) which triggers error #94, 'Invalid use of Null'.

I'm unsure what you want instead. If claims.id2 is a numeric data type, maybe this will work:

nid = Nz(DMax("id2", "claims", "yr =" & yr), 0) + 1