Format autonumber field properties to include 2 digit year

[JDub22]

I am attempting to set the field properties for the autonumber data type to yield an auto number that starts with a two digit year. The 4 digit year will also work. I am trying to get a result that looks something like this:

15-000001
15-000002
15-000003
etc...

or,

2015-000001
2015-000002
2015-000003
etc...

Thanks for your time and I appreciate any help you can give me.

[jforbes]

Hello JDub22,

Here are a couple (three) Questions/Answers that are similar to the one you are asking, they might have what you need:

http://bytes.com/topic/access/answers/959541-autonumber-using-alphanumeric-text-number-data-type

http://bytes.com/topic/access/answers/667130-ms-access-vba-generate-autonumber

http://bytes.com/topic/access/answers/784078-leading-zeroes-alphanumeric-id-number

You could also use an AutoNumber column along with a Date Field that Defaults to the Current Date when the Record is Created. Then calculate the field in the format you want when you need to view it or print it.

[SlingerJM]

I spent three days searching the internet for a solution to my problem. I had some help from our IT expert and I now have a solution. First, let me repeat what I needed. I needed a Condition Report(CR) Number which will drive my database. I created a field "CR Number". I needed information in that field to be prefixed with the current year, by last two digits, a hyphen, and a sequencing number that reverts back to 0001 at the beginning of each year. For example, I needed a number like: 15-0032 and the first CR of next year would be 16-0001.

There are solutions shown on this site, but not a lot of directions for someone like me with little to no coding or Access experience so I am going to detail not only the solution, but "how to" employ it in MS Access 2010.

Not only will this solution provide you with a sequential number and a two-digit year, but the macro below also restarts the sequence when the new year begins.

Directions:

I created a table (CR Database) with a field "CR Number" (data field: text). In the "Datasheet View", I clicked on the field/column for "CR Number". I then went to the ribbon under "Table Tools" and clicked on the ribbon "Table". Icons appeared on that ribbon and I clicked on the icon "Before Change". This is MS Access' Macro developer. Then I worked through the helping screens to insert the following code:



Expand|Select|W rap|Line Numbers

1.   If [IsInsert] Then
2.    SetLocalVar
3.       Name YearPrefix
4.       Expression = Format(Date(), "yy")
5. 
6.    SetLocalVar
7.       Name nextSeq
8.       Expression = 1
9. 
10.    Look Up A Record In  
11.       SELECT z.[CR Number] FROM [CR Database] AS z ORDER BY z.[CR Number] DESC;
12.       Where Condition = [z].[CR Number] Like [YearPrefix] & "-*"
13.       Alias z
14. 
15.    SetLocalVar
16.       Name nextSeq
17.       Expression = Val(Right([z].[CR Number],4)) +1
18. 
19.    SetField
20.       Name [CR Number]
21.       Value = [YearPrefix] & "-" & Format([nextSeq], "0000")
22.    End If

When the screens pop up and you fill in the lines, some of what you are typing (IsInsert, SetLocalVar, Look Up A Record In, SetField, etc.) automatically populates or appears in drop down menus.

I hope this helps in this situation.

[zmbd]

Just to clarify, SlingerJM appears to be using a table level event "data macro" that was introduced in Access 2010 which behave along the lines of "triggers" in the server type databases.

Keep in mind that that, to the best of my knowledge, you can NOT upsize an Access database that uses "data macro" in the table design.

For others, if VBA or SQL methods covered herein are giving you problems, feel free to start a new thread and either post your code and/or reference the thread/post here at bytes that is giving you problems - in doing so, you are helping others too! :)

[NeoPa]

I tend to use DMax() with a Criteria that matches the date part of the value and an Expr that returns the numeric part. The returned value should be converted from a string using something like Val() and a lack of any entries found should be handled by using Nz(..., "0").

So, in your case something like :

Public Function NewKeyVal(strTable As String) As String
    Dim strWhere As String

    NewKeyVal = Format(Date(), "yyyy\-")
    strWhere = Replace("([Index] Like '%Y*')", "%Y", NewKeyVal)
    NewKeyVal = NewKeyVal & Format(Val(Nz(DMax(Expr:="Mid([Index],6,6)" _
                                             , Domain:=strTable _
                                             , Criteria:=strWhere) _
                                        , "0")) + 1, "000000")
End Function