By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,711 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,711 IT Pros & Developers. It's quick & easy.

Format autonumber field properties to include 2 digit year

P: 1
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:




Thanks for your time and I appreciate any help you can give me.
Jan 14 '15 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,107
Hello JDub22,

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

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.
Jan 14 '15 #2

P: 5
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.


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|Wrap|Line Numbers

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

Expert Mod 5K+
P: 5,397
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! :)
May 4 '15 #4

Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. Public Function NewKeyVal(strTable As String) As String
  2.     Dim strWhere As String
  4.     NewKeyVal = Format(Date(), "yyyy\-")
  5.     strWhere = Replace("([Index] Like '%Y*')", "%Y", NewKeyVal)
  6.     NewKeyVal = NewKeyVal & Format(Val(Nz(DMax(Expr:="Mid([Index],6,6)" _
  7.                                              , Domain:=strTable _
  8.                                              , Criteria:=strWhere) _
  9.                                         , "0")) + 1, "000000")
  10. End Function
May 6 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.