Invalid Use of Null Using the DMax Function
Posted on 2006-10-25
I'm having a problem with the following code. It is running a query (qrygetNum) that uses a mid function to separate the number from a document name (XXX-001). It then adds one to this number. The problem I'm having is when the query comes up with no results it gives me an 'Invalid Use of Null' error. When the query comes up with no result I want it to give a document number of '001'. I put the in If IsNull statement, but it's in the wrong spot, or it's the wrong code all together.
Everything else works fine. If there is an existing doc number it adds one to it. The remainder of the code adds the doc name (DocAcro) to the number.
Dim DocNum As Integer
Dim DocExt As String
Dim DocAcro As String
DocAcro = Me.GeoFirstLetter & Me.FuncIden & Me.TypeIden
DocNum = DMax("[qryGetNum]![Number] + 1", "[qryGetNum]")
If IsNull(DocNum) Then
DocExt = "001"
ElseIf DocNum <= 9 Then
DocExt = "0" & "0" & DocNum
ElseIf DocNum <= 99 Then
DocExt = "0" & DocNum
If vbNo = MsgBox("Are you sure you want to create document number" & " " & DocAcro & "-" & DocExt & "?", _
vbYesNo, "Creating Document Number") Then Exit Sub
' Places the value of DocAcro + DocExt in the DocumentNumber field.
Me.DocumentNumber.Value = (DocAcro & "-" & DocExt)