Custom Serial Number Format Based Upon Julian Date.

[MKTetzlaff]

Hello:

I need to create a serial number that auto increments based upon the following format:

Last two digits of the year + Julian day of the year + two digit unique incremented number.

1610604 would be 2016 on the 106th day and the 4th unit and the next number would be 1610605.

I do not believe that we will ever exceed 99 units per day.

I have Access 2016 and believed that I could do it in the Format section of a AutoNumber field.

Any help would be greatly appreciated.

- Mike T.

 

[igold]

Hi Mike,

This formula will give you the first part of your request. I am not sure how you intend to have it incremented. Meaning, is it from a new row or from a a button click, etc..

Nonetheless, perhaps this will help you get started...

=RIGHT(YEAR(TODAY()),2)&TEXT(A1-DATE(YEAR(TODAY()),1,0),"000")

HTH

igold

 

[Joe4]

igold,
This is an Access question, not an Excel one (note that it is posted in the "Microsoft Access" forum).
If you are getting to these questions using the "Zero Reply Posts" listing, note that it lists what forum they appear in over to the far right.

 

[Joe4]

I have Access 2016 and believed that I could do it in the Format section of a AutoNumber field.

If you need it to start at "00" or "01", and you need it to go in order and not skip any numbers, I doubt you will be able to use Autonumber. Autonumber must be unique, so it cannot reset each day, and you cannot "assign" values to Autonumber. Access does it automatically.

Does this value need to be a hard-coded table field, or can it be an Access calculated field in a query (if it can be a calculated field, the "date" field you need to be stored somewhere, if it is not already done so)?

Also, how exactly if your data entered?
Manually through a Form?
Imported?

 

[igold]

igold,
This is an Access question, not an Excel one (note that it is posted in the "Microsoft Access" forum).
If you are getting to these questions using the "Zero Reply Posts" listing, note that it lists what forum they appear in over to the far right.

Yes, I am getting the questions from the "Zero Reply Posts" and no I did not pay attention to the forum in which the post was in.

I appreciate you pointing that out to me. I will clearly try to be more careful in the future.

Stuff happens.

igold

 

[MKTetzlaff]

Hi Joe:
I thought I posted a reply but I do not see where it went.

The serial numbers for today are: 1608901, 1608902, 1608903, 1608904,...
Tomorrow the serial numbers will be: 1609001, 1609002, 1609003, 1609004,...
The format is the last two digits of the year (16), the Julian day (090 for 04/01/16) and then a sequential number starting at 01.

The value can be calculated and stored in a field (the other field in the data base is the series name (PL7, B4, MB8, etc.).

I had not given any real thought to how it will be entered, I would imagine through a form would be the easiest.

I envision two buttons, one to print and one to create a new record (populated with the serial number in the above format).

Thanks,

- Mike T.

 

[Joe4]

What is the name of your table and the name of this field which will hold the custom serial number?

 

[Joe4]

OK, as long as you are controlling the input of new data via Forms, this can be done in the following manner.
I am making the following assumptions, which are built into my solution. So you will need to make changes to the names where necessary.

Name of data table: Table1
Name of Serial Number field: SerialNum

First, go into the data table, and change the Properties of the SerialNum field so that the following Properties are set:
Data Type: Number
Required: Yes
Indexed: Yes (No Duplicates)

Now, create a new query that returns all records from your data table that have a SerialNum value with today's date in it. Do that by:
- Create a new query based on Table1
- Double-click the SerialNum field in order to return it in the query grid
- On the Criteria row of the query grid under the SerialNum field, add this formula:

Like Format(Date(),"yy") & Format(Date()-DateSerial(Year(Date())-1,12,31),"000") & "*"

- Save the query as qryMaxSerialNumber

OK. Now go to your entry Form. Make sure that you add the SerialNum field to the Form, and it is bound to the underlying field in Table1.
Go to the Properties of this SerialNum text field and set the Enabled property to "No". This will prevent users from editing it.
Add a Command Button to your Form. This will be used to populate the SerialNum field so that the record can be added (remember, we made it a required field).
Go to the Properties of the Command Button, go to the Event tab, and choose the [Event Procedure] option in the "On Click" row, and enter the following VBA code between "Private Sub ..._Click()" and "End Sub":

    Dim RecCount As Long
    Dim JulianDate As String
    Dim NextSerialNum As Long
    
'   If SerialNum is already populated, exit
    If Me.SerialNum.Value > 0 Then
        MsgBox "Record aleady has a SerialNum value assigned to it"
        Exit Sub
    End If
    
'   Count number of records added with today's date
    RecCount = DCount("SerialNum", "qryMaxSerialNumber")
    
'   Calculate next record number
    If RecCount = 0 Then
        JulianDate = Format(Date - DateSerial(Year(Date) - 1, 12, 31), "000")
        NextSerialNum = Format(Date, "yy") & JulianDate & "01"
    Else
        NextSerialNum = DMax("SerialNum", "qryMaxSerialNumber") + 1
    End If
    
'   Populate SerialNum
    Me.SerialNum.Value = NextSerialNum

This code should do what you want.

 

[xenou]

Beware that whatever you are doing you will be stuck at 99 items. Think about the future!

 

[Joe4]

Beware that whatever you are doing you will be stuck at 99 items. Think about the future!

From original post:

I do not believe that we will ever exceed 99 units per day.

If you think the possibility exists that you may ever go over that, you may want to extend the last part to three digits instead of two, etc.