Custom Sequential Number with Year-Numbers-One Letter

[Familia Roca]

I am having such a hard time trying to get this custom sequential Number. Please help!
The first Part gets the two digits from the current year, the middle part gets the max number and the last part the first letter of the value.
My control is a box with textbox short text datatype, but I cannot get the middle portion to work. The intended result needs to be something like 24-008763-S , 24-008764-N, or 24-008765-T in order to have every year a new number and or the middle portion resets to 0.
I have a Autonumber ID already. This is just for Displaying on forms and reports. The Student Type is entered after other fields have been entered, so the code goes on the save event.
Thank you so much for your help.

this is what I have come up with so far:

on save

Dim StudentNo As Long
Me.StudentNo.Value = Right(Year(Date), 2) & "-" & Format(Nz(DMax("Val(Mid(StudentNo,2))", "StudentsQB") + 1, "000000")) & "-" & Left(Me.StudentType.Value, 1)
or
'Me.StudentNo.Value = Right(Year(Date), 2) & "-" & Format(Nz(DMax("Val(Mid(StudentNo,2))", "StudentsQB") + 1, "000000") & "-" & Left(Me.StudentType.Value, 1))

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Eugene-LS]

this is what I have come up with so far:

If your values luck like "24-008765-T" - you have to take the number from the fourth character, not the second. It should be only 6 characters, not all of them as you have.
Mid(StudentNo, 2) <> Mid(StudentNo, 4, 6) ...

Mid() Function

Me.StudentNo = Right(Year(Date), 2) & "-" & _
    Format(Nz(DMax("Val(Mid(StudentNo,4,6))", "StudentsQB") + 1, "000000") & _
    "-" & Left(Me.StudentType, 1))