Incrementing Number (1 Viewer)

n_lou · Jan 8, 2010

Hi I am hoping someone can help me, I am trying to creating an incrementing number using dmax which resets each year. Using the idea suggested on post linked below

http://www.access-programmers.co.uk/forums/showthread.php?t=67675&highlight=sequential+numbers+DMax+year

I have managed to create a number format which looks like 2010-1, 2010-2 etc using the following formula.

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.DDRNumber = Format(Year(Date), "0000") & "-" & _
Format(Nz(DMax("Mid([DDRNumber], 6)", _
"tblDDR", _
"[DDRNumber] Like '" & Format(Year(Date), "0000") & "*'"), 0) + 1, "0")

End Sub

However I would rather the year came after the number eg.. 1-2010, 2-2010 but can not work out what I need to swap on the formula, it keeps bringing up errors.

Is anyone able to help me please ?? Happy to split the year and incrementing number if this would help.

Poppa Smurf · Jan 8, 2010

Have you tried the following?

Me.DDRNumber = Format(Nz(DMax("Mid([DDRNumber], 6)", _
"tblDDR", _
"[DDRNumber] Like '" & Format(Year(Date), "0000") & "*'"), 0) + 1, "0")
& "-" & _
Format(Year(Date), "0000")

Steve R. · Jan 8, 2010

n_lou said:
Hi I am hoping someone can help me, I am trying to creating an incrementing number using dmax which resets each year.

Here is what I use to auto-increment with a yearly reset. Of course, you will need to modify. The format of the output looks like this 2009,0185, 20090186, 20100001, 20100002, etc.

Code:

Function newprojectnum()
    Cyear = Year(Date)
    Lastnum = DMax("[projectnum]", "consistency")
    TestYear = Val(Left(Lastnum, 4))
    Rem Test to see if too many numbers
    If Val(Right(Lastnum, 4)) >= 997 Then
        DoCmd.Beep
        MsgBox "The number of projects exceeds 997. If valid - fix programming."
        DoCmd.Quit
        End If
    Rem incrementing the project number for current year
    If TestYear = Cyear Then
        newprojectnum = Lastnum + 1
        End If
    Rem incrementing at the start of a new year
    If TestYear <> Cyear Then
        newprojectnum = Val(Str(Cyear) + "0001")
        End If
End Function

Incrementing Number (1 Viewer)

n_lou

New member

Poppa Smurf

Registered User.

Steve R.

Retired

Similar threads

Users who are viewing this thread