1 Replies - 523 Views - Last Post: 19 May 2016 - 12:45 PM

#1 qsmrf  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 77
  • Joined: 09-October 15

MS Access Question regarding autonumbers

Posted 19 May 2016 - 02:58 AM

Ok so I want an auto key that first has the year in it and then the actual number that is to be displayed like this

"[year]"-0000 <------ This was the format

16-00001 <-------------- This is the example

now I am able to do it but I wanted an extra functionality which is,

after the year (i,e = 16) is finished, i would like it to automatically change it like this

17-0000 <-----

is there a way to do this? if so, then please tell em

Is This A Good Question/Topic? 0
  • +

Replies To: MS Access Question regarding autonumbers

#2 andrewsw  Icon User is offline

  • lashings of ginger beer
  • member icon

Reputation: 6338
  • View blog
  • Posts: 25,552
  • Joined: 12-December 12

Re: MS Access Question regarding autonumbers

Posted 19 May 2016 - 12:45 PM

Edited: Ignore my following description. I've realised that you want the autonumber to restart from 0 each year. There isn't an automatic way to do that. Even if you create a retrieve the next available sequence number from a table, you would still have to manually reset it each year.

If it isn't essential for the sequence number to restart each year then just let them continue.

You cannot do this with a single field, you need to combine two fields for display and set the combination of the two as a unique index or primary key, assuming this is your intention.

You would have the first field as an autonumber field, and the second with a default value of Year(Date()). Then, on a form or in a query, you can use an expression to combine and format the two values. Because you are using the Date() function, and its Year(), it will change to to 2017 next year; use Format() to extract just the two-digit year and concatenate the 4-digit (or 5-digit) autonumber to it. E.g. (untested) Format(Year(Date()),"yy")&"-"&Format(id,"0000").
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1