Increment a number, reset each year.

[MikeGeitner]

Hi,

I'd like to use the example in thread702-827975, kindly given by Bob Scriver, in my project. The only difference I need to work out is the format of the year part of the string. I'd like it to be YY-000, instead of YYYY-000. I should be in a VB class right now, but instead I'm beating my head against the desk.

Any help is surely appreciated.

Mike

 

[PHV]

And where is the problem ?
Tip: Format(Date(), "yy") instead of Year(Date())

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

As an [blue]IIf[/blue] statement:

[blue]=IIF(Format(Date(),"yy")=CInt(Left(DMax("[SepticPermit]","[Septic]"),2)),Format(Date(),"yy") & "-" & Format(CInt(Right(DMax("[SepticPermit]","[Septic]"),3))+1,"000"), Format(Date(),"yy") & "-001")[/blue]

As a function:

[blue]Public Function NewID() as String
   Dim curYr As String, Ary
   
   curYr = Format(Date, "yy")
   Ary = Split(DMax("[SepticPermit]", "[Septic]"), "-")
   
   If curYr = Ary(LBound(Ary)) Then
      NewID = curYr & "-" & Format(Int(Ary(UBound(Ary))) + 1, "000")
   Else
      NewID = curYr & "001"
   End If

End Function[/blue]

See Ya! . . . . . .

 

[MikeGeitner]

Thanks! I'll try it!