autonumber field question

[Dana]

Can I set it up so that a field will show a two digit year
then a dash and then autonumber? For example the
autonumber field will look like thise: 04-1 then the next
would be 04-2.... I just want it to always use the two
digit year dash format ahead of the autonumber.
Thanks for any help you can give.

 

[Jim]

You will need to have a custom function to do this
Check Q88169 of KB for an idea

 

[Dana]

I went through that and it doesn't do quite what I would
like it to do. I would like it to automatically throw up
the two digit year, then dash and generate an autonumber
at the end. When I was using the KB file, you have to
predermine numbers in a seperate table. The access
autonumber is fine the way it works, I just want it to add
the two digit year to the beginning of it.
Thanks for you help.

 

[Bruce M. Thompson]

I went through that and it doesn't do quite what I would

like it to do. I would like it to automatically throw up
the two digit year, then dash and generate an autonumber
at the end. When I was using the KB file, you have to
predermine numbers in a seperate table. The access
autonumber is fine the way it works, I just want it to add
the two digit year to the beginning of it.
Thanks for you help.

The autonumber field type generates values that are not intended to serve as
anything other than unique record identifiers. What you want can only be stored
in a text field. The method provided by the KB article is pretty much how to
approach what you want, but there are other methods using code and sql to
perform the same task. If you want to give the appearance of a record identifier
using an autonumber field, you can save the 2 digit year value in a separate
text field and either place it to the left of the autonumber field control when
displaying it, separating the two by a "-", or display the whole "number" in a
calculated control everywhere you need to display it using a text box with a
"Control Source" property set to something like this:

=[txtYear] & "-" & [MyAutonumberField]

Incidentally, autonumbers are notorious for gaps in values due to deleted
records or records that were started, but not saved (the record was "undone" in
the form instead of saving) and once that has been done, that "next value" has
already been used up. If that is not acceptable for your purposes, you will need
to generate your own incrementing numbers on-the-fly (one approach is
demonstrated in that KB article and is similar to the approach that I use for my
incremental number generating implementations).

 

[Douglas J. Steele]

If you really need what you're describing, store the year as a separate
field, and concatenate the year and autonumber for display purposes.

And just in case your next question is going to be "How do I reset the
autonumber to 0 next year?", you can't. There's no way to change how an
autonumber works.

 

[Peter Copland]

If you want a record tath would folow the follow sequence
200401001
200401002
200401003
..........
..........
Nxet month roles over
200402001
200402002
200402003
..........
Next month roles over
200403001
200403002
200403003
..........
..........
New year roles over
200501001
200501002
200501003

and so on

you could try creating a table with a single field, the
table
would be called for example JNTracking. This table would
hold the last JN issued. The field could be called
MaxJobNumber for example.

Then create a form with 3 control sources

with the following names for example

NewJN - source = IIF([First] > [ADD1],[First] + 1, [ADD1])

First - source = Year(Now()) * 100000 + month(Now())* 1000

Add1 - source = [MaxOfJobNumber] + 1

A new number is issue on the basis of is this a new year
or new month, if so increment the relevant parts of the
field, if not just add 1 to my previous JN

Create a macro to fire off the new JN and add it to the
table where it needs to be applied