Generate unique ID based on year, number reset each year

[anguyen]

Hello everyone,

I want to generate a unique ID number for a test number field in the format NTN-YY-XXX where YY is the two digit of the year, and XXX is the sequential number. This number reset to 001 each year. For example, the first row generates this year would have the number NTN-12-001, next one would be NTN-12-002 ... Next year, the first number would be NTN-13-001. Would someone kindly show me how to achieve this?

I started with this: "NTN-" & Right$[DatePart("YYYY" , NOW(),2)
but don't know how to but in the auto increment part and has the increment reset.

Thanks,
Al

[NeoPa]

In real terms what you do is to determine the current year, then look in the table to see the maximum value used so far that matches the format for the year. Make sure to treat the situation where no records are found as returning a valid string in the correct format. With the returned value simply take the number part and add one to it for the information required to build the new key with.

Assuming you have a table called [tblData], with a PK called [PK] (as you didn't share this information in the question unfortunately. Please make sure to include all relevant info on future questions.) then the code might look similar to :

Private Function NewPK() As String
    Dim strTemplate As String

    strTemplate = Replace("NTN-XX-", "XX", Right(Year(Date()), 2))
    NewPK = Nz(DMax(Expr:="Right([PK], 3)", _
                    Domain:="[tblData]", _
                    Criteria:="[PK] Like " & strTemplate & "*"), "0")
    NewPK = strTemplate & Format(Val(NewPK) + 1, "000")
End Sub

[anguyen]

Yes, I should be more clear, thank you for your help!

[anguyen]

I got a syntax error (missing operator) in query expression '[PK] Like NTN-12-*. I have the table [tblData] and [PK]. VBA is new for me, I apologize if I ask silly question; I mostly deal with Oracle.

[NeoPa]

Not a silly question at all. I made a complete nooby error when I posted that code. While you read my updated version I'll just go out the back and beat myself with a stick for the embarrassment I just caused myself :-D

Private Function NewPK() As String
    Dim strTemplate As String
 
    strTemplate = Replace("NTN-XX-", "XX", Right(Year(Date()), 2))
    NewPK = Nz(DMax(Expr:="Right([PK], 3)", _
                    Domain:="[tblData]", _
                    Criteria:="[PK] Like '" & strTemplate & "*'"), "0")
    NewPK = strTemplate & Format(Val(NewPK) + 1, "000")
End Sub

[anguyen]

Thanks, that did the trick with the debug editor, but I tried to enter the data from table, the PK did not populate automatically? Do I need to refer the function name from the PK to trigger it? I'm entering a different world of database.

[NeoPa]

This question is a new one and doesn't relate to the original one (There is no information provided from which to answer you). I suggest you post a new question (You may link to this if you like.) where you include the code you have that calls this procedure (as that's what will determine populating the [PK] field).