Creating my own unique auto-sequential Notice Number

[KerryL]

My database records company layoff or closing notices. The primary key is an autonumber that I don't visibly use.

However, the notices do need to be uniquely numbered, and my agency would like them numbered as follows:

Notices in 2006 will be numbered 20060001, 20060002, etc.
Notices in 2007 will be numbered 20070001, 20070002, etc.

What's the best way for me to add a field to the table that will accomplish this and will allow me to change it with each new year?

I checked the FAQs but didnt see anything similar to my request. Thanks for your help,
kl

 

[barny2006]

igpcs, this will accomplish what you have listed:
B = Year1 & right("000" & NNNN,4)

 

[KerryL]

barny2006,

RE: B = Year1 & right("000" & NNNN,4)

Can you help me understand how your code works?

 

[KerryL]

barny2006,

re: B = Year1 & right("000" & NNNN,4)


I'm not sure I follow you. Can you explain to me how your code works? Thanks.

 

[IGPCS]

kerry did my suggestion help you? or its still not working good?

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[barny2006]

it concatenates "000" and your number
example 0000123
then takes rightmost 4 digits of this new string which is
0123
right(0000123,4) which is 0123

 

[barny2006]

b = year1 + rightmost 4 digits of (000 + your number) = 20060123
if your number is 2 then: 20060001
if your number is 1234 then: 20061234
i already test this. it works for any 4 digit number.

 

[MichaelRed]

so don'ty look into the faq(s) and continue to thrash about ...




MichaelRed

 

[barny2006]

michael,
i'm not sure if "autonumber" will solve this problem. and i don't know the relevance of dlookup to generating a custom made numbering scheme.

 

[MichaelRed]

so don'ty look into the faq(s) and continue to thrash about ...




MichaelRed

 

[KerryL]

Michael,
Thanks, I did look in the FAQs but didn't find exactly what I was looking for.

Turns out the users want the ability to re-set the numbering sequence at the beginning of a new year. IOW, if 2006 ended with NoticeNo = 2006325, they want to simply enter 2007001 for the first entry of 2007 and then have the program automatically increment NoticeNo for each additional saved record. (In 2008 they'll manually enter 2008001 for the first record, and so on.)

So, basically my task is to have the program increment the Notice Number of each new record by +1 each time a record is saved.

db = WARN_Vers11.mdb
tbl = WARNData
field = NoticeNo


Thanks in advance for your help!

 

[MichaelRed]

at least one faq does this (and a bit more) ... which should make customization of the routine quite easy. The one I refer to actually "rolls over" the counter on the first of each month, so the only isses I see (from here at least) is to change the roll over condition (from first of the month) to first of the year AND make sure the size of the value holding the max value is sized sufficiently to accomodate a years' worth of records (I believe it is set to integer in the faq, but should be a Long Integer for a year).

sorry you couldn't find or understand it.


BTW, all the procedures which rely on finding the current max value withi the table suffer from two flaws. first, as the recordsets grow, the routine to extract the max value will take more time. this one is mostly an annoyance, to the extent that it is ever noticed. the second, however, is fatal in multiuser systems, as (particularly with the larger recordsets) that the routines can (and inevitably WILL) retrieve the same value and the process will at least return an error condition to one of the users and may cause db corruption.




MichaelRed

 

[KerryL]

I assume you meant this one:

http://www.tek-tips.com/faqs.cfm?fid=184

However, that FAQ seems to be overkill for what I'm trying to do, since I'm not automatically updating the numbering scheme every year (it will be done manually).

I was just looking for a simple routine to automatically increment a data object in the table. And because I'm not very good at declaring datasets etc, I was hoping someone could help me with that part of it.

Sorry if you misunderstood.

 

[MichaelRed]

good luck



MichaelRed