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|Wrap|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.