Auto number?

[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

 

[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

 

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

 

[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

 

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

 

[Joseph Meehan]

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

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.