How to Sequential Numbering that includes Month Year and resets every year

[phsyche_12]

How can I create a sequential numbering that includes month and year, and it should reset every year.
Sample: 0001, 0002, 0003, and so on.... then Month, 01, 02, and so on... then Year, 2023, 2024, and so on...
Shows like this: mm-sequence number-yyyy
February 2023: 02-0001-2023 note: 0001 is the unique ID Autonumber

Thanks in advance

 

[Gasman]

You cannot use an autonumber in that case for that.
You would likely use DMax() +1 with criteria for the year.

 

[phsyche_12]

You cannot use an autonumber in that case for that.
You would likely use DMax() +1 with criteria for the year.

Thank you, I only have few knowledge about MS Access, Since I am a newbie user....I tried some VBA but I can't figure out how to do it, may you please help, Thank you...

 

[Gasman]

You have a table that holds at least
Your sequence number
Your Year
You would also use the NZ() function in case the number is not found, as in the case of a new year, or you could populate the table with 0 and years for as long as you need it. If the number is not found, then you would create a new record with the year and zero.

Then you use DMax() (Google the syntax) to get the number for the year and add 1 to it.
If multi user db, only do it just before you save the record, else, someone else could get the same number, if the table number has not changed.

sequential number access - Google Search

 

[CJ_London]

The problem with maintaining sequential numbers is if a record is deleted you then have a gap so your numbers are no longer sequential.

also, your ‘number’ will be text - which takes up more space, and given your format, will be slower to search unless you always include the month in your search term. Reason being indexing cannot be used if the month is not included

if you really must go down this route, recommend you have 3 columns - month, counter and year, then concatenate as and when required for display purposes. They will take up less space and faster to search.

Do not use these 3 columns as a primary key, it will make managing your relationships very difficult. Just use an autonumber primary key and set a unique index across the 3 columns

 

[Minty]

If you keep it as three fields and don't bother with the reset every year, you could use the Autonumber as the middle section and not have to worry about it.

Does it matter if you skip a number, if someone deletes a defective entry, probably not so why worry it about it?
Keep everything simple!

You can format it to for viewing with leading zero's if there is a aesthetic reason to.

 

[isladogs]

I use the same system as Microsoft does with Office 365 version numbers.
I use a YYMM integer number for each year/month. Currently it is 2310 and in Jan will be 2401
Although you cannot do this with Autonumber fields, using that approach guarantees entries are sequential

If you need multiple records for each month, add a separate column with a number field then in queries you can concatenate (combine) the two fields using e.g. 2310-01, 2310-02 etc

 

[The_Doc_Man]

I'm in agreement with the others. The most efficient way to do this is to recognize that the YYMMnnnnn sequence number has no meaning to the computer, it has meaning to YOU because it is a visual aid. So keep the YYMM portion as a separate field from the sequence number and only format them for display or printing purposes. But if there is a potential for child records (dependent records, if you prefer) then you need a separate field as an internal key - which is best served via the autonumber methods.

In fact, if this record has a date field within itself to note when it was created and that date won't change after the fact, you already have YY and MM available anytime you need them. If the date CAN be changed after the fact, then you DO need separate YY and MM or YYMM combined fields to track it.

 

[Pat Hartman]

Here is a sample that shows how to generate a custom sequence number that resets within the values of a different field.
The others have all made valid points that you need to consider. But, sometimes the boss just wants what he wants

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

 

[phsyche_12]

Thank you very much! I've tried some of them but I use this sample as not to complicate things, and will not consider a reset every year, but I still have a problem...I want to format ID (Autonumber) as "001, 002, 003 and so on, and I already did that, I use concatenation, but the problem is it only shows, mm-ID-yyyy, as
10-1-2023, 10-2-2023, and so on...
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.PerDate = Format(Date, "yyyy")
Me.PerMonth = Format(Date, "mm")
----> Me.ID = Format???????
End Sub

Thank you...

 

[June7]

Format(ID, "000")

 

[phsyche_12]

Format(ID, "000")

Me.ID = Format(ID, "000")... there's a problem when I am using this Syntax

 

[Steve R.]

How can I create a sequential numbering that includes month and year, and it should reset every year.
Sample: 0001, 0002, 0003, and so on.... then Month, 01, 02, and so on... then Year, 2023, 2024, and so on...
Shows like this: mm-sequence number-yyyy
February 2023: 02-0001-2023 note: 0001 is the unique ID Autonumber

The purpose of the forum is to provide you with suggestions on how to solve a question you pose. In this case, it would appear that you are asking for a solution that circumvents the appropriate use of the autonumber field (assuming that you are using it as an autonumber field). As an alternative suggestion let the autonumber field do its thing and basically forget about it, more or less. Create a "year" table and created a "project number" field in your main table; linking the year to the project number. You can then concatenate the two to display your results as you want.

 

[Gasman]

I would say the same. Leave the autonumber for it's purpose and have your own sequential number, that you can format as you want.

 

[June7]

Me.ID = Format(ID, "000")... there's a problem when I am using this Syntax

Using it where? What problem? Can't save this to a number field. Use it in a query or textbox for display.

 

[The_Doc_Man]

Me.ID = Format(ID, "000")... there's a problem when I am using this Syntax

June7 commented here as well.

If the raw numeric ID field is called "ID" then you are formatting inappropriately. You don't format a number when you store it. You format a number when you are going to display it in a query or in a control on a form or report, and you can adjust the displayed format without changing anything that you had stored.

I'll be a little bit blunt here because apparently a previous point didn't get across.

You don't format ANYTHING internally in a table because you will probably use the contents for computation or searching. You leave numbers as numbers (INTEGER, LONG, etc.) so that the CPU can do a numeric comparison or lookup. When you want to SHOW a number to a person, THEN is when you use functions like FORMAT().

Computers see things two ways - as a computational value or a presentation value. To make things work smoothly, learn which is which and perform operations according to the immediate purpose.

I will offer another comment that looks to the future. Calling a field ID works until you have another field in another table and it is also called ID. You will learn to make ID fields have a little more in their names so you can tell multiple ID fields apart. Like if you have a person table then the ID might be PersID. If you have a building ID, you might use BldgID. If you have a sequential record ID it might be RecID. Or in your case with that semi-sequential Year/Month factor, maybe YMID or YMSeq. Why, you ask? Because if your project has to be shuffled aside due to some higher priority thing taking your attention away for a while, when you come back to this project, you will have less trouble remembering what was going on if the names have mnemonic value - if they have MEANING.

 

[CJ_London]

To expand Doc's comment

if they have MEANING.

if they have MEANING WITHIN THE CONTEXT OF THE APPLICATION

 

[apr pillai]

An example, may be helpful with few changes: https://www.msaccesstips.com/2012/11/autonumber-with-date-and-sequence.html