Serial Number to be reset with each day

[av123]

Hi everyone.
What I need to do is that I need to generate a number which contains date,month,and then a serial number which needs to be reset with each new date.
For eg 280901 (28-date 09-month 01- serial number), 280902, 280903...
and then 290901 for the next date
I am new to VB.
Please help

[mangesh.mehendale]

Is it okay if it is done with excel function... but caution is you have to remove the formulae on same day... refer attached file

Please Login or Register  to view this content.

[av123]

Hi Mangesh. Thanks for the code but I need it in VB macro and not an excel formula

[mangesh.mehendale]

Okay... just hit on Generate button.... in attached file....

Please Login or Register  to view this content.

[av123]

Thank you so much!

[mangesh.mehendale]

Welcome... Just give a time to Click no "Add Reputation" to answer.....

[Jake Bruce]

This post is from 2016 but it was helpful today in the year 2022. Thank you for the code. I tried it and understood how each line works. However, it seems like there was no code to reset the serial key to 01 for each new month. Can you check it please if you are still active in this forum, thank you.

[mangesh.mehendale]

Hi Jake.. Can you please provide example for better clarity..?

[Jake Bruce]

Hi Jake.. Can you please provide example for better clarity..?

Hi, thank you so much for your reply.

I'll take the same example as the original thread creator but with better clarity. For example, 280901 (28-date 09-month 01- serial number), 280902, 280903 and it goes on. I would like only the serial number to be increased by 1. In the code you shared, it calculates it as 280901 + 1. When it reaches 280999, adding + 1 would be 281000. However, we want to continue it as 2809100 for that day. The next day would be the 29th, so the serial key should reset back to 01 which will be like 290901.

I hope the above example is clear. I'm also trying to achieve something similar to it but instead of DDMM, I'm doing it with the YYYY. For example, 2022001 (2022-year and 001-serial number), 2022002, 2022003.... 20221000, and so on. The next starting year will be like 2023001 where the serial number willl reset back to 001.

[Jake Bruce]

Hi, never mind I managed to somehow do it with the logic suggested in another post.

Link: bytes.com/topic/access/answers/584213-reset-file-number-zero-each-year