Next Number in series

  • Thread starter Thread starter daksport00
  • Start date Start date
D

daksport00

This may be something that is very easy to do, may not be, but either
way, it has got me stumped.

I am trying to come up with a form/function/query/task/table (whatever
you would like to call it), such that from a form, I can enter
information pertaining to a drawing, and access will assign it a unique
drawing number.

The format for the drawing number is: SK-(last 2 digits of Year)(2
digit Month)(next number in series). I.E> SK060401, SK060402, etc.

Can anyone provide me assistance here???
 
This is one of the most frequently asked questions. I'm suprised you couldn't
find the answer with a search.

It doesn't matter where you do it, the principle is the same. You use the
DMax function to find the highest current number and add 1 to it:

strDrawingNo = "SK" & Format(Date,"yymm")
strLastNo = Nz(DMax("[DRAWING_NUMBER]", "tblDrawings", _
"[DRAWING_NUMBER] Like '" & strDrawingNo & "*'"),"")

If strLasNo = "" Then
strDrawingNo = strDrawingNo & "01"
Else
strDrawingNo = strDrawingNo & Format(Cint(Right(strLastNo, 2) + 1, "00")
End If
 
Back
Top