Auto number?

C

Cnone

Is it possible to modify auto number system, like giving numbers with a
certain criteria.
Ex: first 4 numbers will be the current year , after that there will be "-"
and the next 5 digits will be incremental numbers. But, also it should
update first 4 digits which will show the current year must be updated
automaticly every year?

Thanks,
Sinan
 
N

Nikos Yannacopoulos

An autonumber table field is not configurable, it's just what it is and
nothing can be done about it. If you are going to be entering new data
through a from, though, you can use a palin text field, and make use of
the default value property of the control bound to it on the input form.
Assuming the table is called MyTable, and the PK field is called
ID_Field, you need an expression like:

=Format(Date, "yyyy") & "-" & Format(Val(Right(DMax("[ID_Field]",
"MyTable", "[Left([ID_Field], 4) = Format(Date, "yyyy")")))+1, "00000")
(this is all in one line, watch out for wrapping in your newsreader!).

HTH,
Nikos
 
V

Van T. Dinh

Not AutoNumber Field.

You have to set up your own Custom Number Sequence.

Search Google as there were plnety of threads on Custom Number Sequence.
 
M

Madhivanan

try this

select
convert(varchar(4),year(getdate()))+'-'+convert(varchar(5),convert(numeric,max(right(number,5)))+1)
from table

where number is the auto number column

Madhivanan
 
C

Cnone

Thanks, but i have a little problem at first i tried to do it as an
incremental number squence but after all the coding finished it started to
give an erorr message sayig that "you cancelled previous operation" "your
query generated an erorr"l""
that's why i wanted to do it in an auto number squence.
 
Top