AutoNumber Format

[jcbnc]

I would like to format the AutoNumber field so that it shows the current year - #...i.e. 2012-01


Is that possible?

 

[boblarson]

Use separate fields and concatenate them when you need to display. But the key here is - do you care if the autonumber suddenly becomes -23289232 for example?

1, 2, 3, -23289232, -23289231, etc.?

Because if you do, then you are using the autonumber for something it shouldn't be used for. You would want to use the DMAX + 1 method. There are a lot of threads around that here.

Autonumbers should be used when you need a UNIQUE number. That is all. They work well as surrogate keys and, if left to work behind the scenes, usually function well. It is when people try to assign meaning to them that it becomes a nightmare. Because autonumbers are not guaranteed to go in sequential order, although a lot of the time they do. They are not guaranteed to be positive, but most of the time they are.

 

[jcbnc]

I doubt the number will get that big. They just want to have a way to create a distinct number that they can quickly sort by year.

 

[boblarson]

I doubt the number will get that big. They just want to have a way to create a distinct number that they can quickly sort by year.

Big isn't what I was going for. You can have 3 records and the autonumber will go to -2762382. Normally it won't. But it can. And has to others. So, if they don't mind seeing

2012--2762382

then you can use it.

or if gaps are not a consideration -

1, 2, 3, 4, 6, 7, 8, 10, 12, 15, 16, 17, 18, 21

autonumbers are assigned when a record is started. If the record is canceled or deleted, that autonumber is gone - forever.

then you can use them.


I guess I am trying to say that, in most cases, an autonumber should NOT be seen or used by anyone/anything other than the database system to maintain referential integrity.

 

[John Big Booty]

Have a look at the sample posted in this thread for an example of how you might use the DMax() +1 to create a sequential numbering system, as suggested by Bob.