I wanted to format an autonumber for the database at my work. It would start with "H"YY, then the day of the year, and then 3 digits to count the number of the entry. For example today's 6th entry would look like this: H00289006 (Today is the 289th day of the year). Thanks you any tips. [sig][/sig]
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Formatting an autonumber 1
- Thread starter kanders
- Start date
-
1
- #2
MichaelRed
Programmer
kanders,
Strictly speaking, autonumber is a keyword in Ms. Access and refers to a value (Number) generated by the database, so you cannot "format" it. I have written a FAQ (in this forum) detailing how to generate a custom version of an autonumber which is similar to your request. Please refer to the FAQ.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
Strictly speaking, autonumber is a keyword in Ms. Access and refers to a value (Number) generated by the database, so you cannot "format" it. I have written a FAQ (in this forum) detailing how to generate a custom version of an autonumber which is similar to your request. Please refer to the FAQ.
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]
Today because of the speed of computers it is not really necessary to "tell a story" with your part number. That's why we all love Access so much cause its easy to find anything in it.
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]
Following up on DougP's response, "smart" numbers or IDs are really just a hang-over from pre-randon-access-relational-db times, when an ID had to be meaningful so that it would relate to things in people's brains, because it was so slow to manually look up an item. Modern systems tend to use meaningless IDs because this makes the system more flexible. For instance, if you number the item with something that refers to a date, type, or quality, that attribute may change. Also, if you allow only 3 digits per day, some day that volume may change. You may find this to be an education issue for your users. [sig][/sig]
In addition-
The AutoNumber fields aren't great for multi-user databases, as DougP states, when useing the default properties (see his explaination), however- You can change the New Data property from "Increment" to "Random", and nearly 1 out of 100000 times avoid the scenario that he had stated. (okay, I might be off on the exact chance, but, it's pretty darn im-plausible). The numbers look even more funky and off-beat than the Incremental ones, but- those incrementals can be off-beat too- ie, if you start a record, and cancel it, Access will retain that Increment, and not allow you to use it again (unless, you compact your database after each time you cancel a record. Then you can start off on that next number). All in all, I'd just set up seperate fields for the different sections of your "smart" numbers. You can then just concatinate them in your output if you wanted them togethor on reports, querys, etc.
The AutoNumber fields aren't great for multi-user databases, as DougP states, when useing the default properties (see his explaination), however- You can change the New Data property from "Increment" to "Random", and nearly 1 out of 100000 times avoid the scenario that he had stated. (okay, I might be off on the exact chance, but, it's pretty darn im-plausible). The numbers look even more funky and off-beat than the Incremental ones, but- those incrementals can be off-beat too- ie, if you start a record, and cancel it, Access will retain that Increment, and not allow you to use it again (unless, you compact your database after each time you cancel a record. Then you can start off on that next number). All in all, I'd just set up seperate fields for the different sections of your "smart" numbers. You can then just concatinate them in your output if you wanted them togethor on reports, querys, etc.
I guess I should restate my view in my last statement:
Actually, when I referred to DougP, I meant to refer to MichaelRed.
After a short e-mail to him, and requesting his view on the AutoNumber, with the Random setting for "New Values", he came up with the perfect response, and one I usually live by- "If it can go wrong -- It WILL go wrong". I agree, so his remarks ring all too true! Check out his "FAQ". He's got a great function that replaces Access's normal AutoNumber.
Though I'm very impressed with his function (and the great commenting that is usually overlooked), I have one sore point I need to bring up about it :-( - it does not matter too much if you don't care if the Date/Time portion of the ID is in cronological order though :-D , since he does have an incrementation based on the actual values.
If, you live in an area that uses DayLight Savings Time (as most areas in the United States do)... On the days that you set time back an hour, your cronological order would be lost during that ONE time in the year. I'm sure for most users this does not matter much, but if you're designing a db that can plausibly have multiple transactions at 2:AM (I think that's when the time gets set back), you can't rely on the crono. order of transactions for that time period.
As this is such a minute period of time, only once a year, I think most people can safely neglect this. However, if you work somewhere, where you have a 24 hour operation, be wary of it!
Always consider DST when designing a system that leaves no room for error on the Date/Time stamp of records (such as Audit Logs).
MoGryph
- "Don't mess with my crew-cut [8O)"
Actually, when I referred to DougP, I meant to refer to MichaelRed.
After a short e-mail to him, and requesting his view on the AutoNumber, with the Random setting for "New Values", he came up with the perfect response, and one I usually live by- "If it can go wrong -- It WILL go wrong". I agree, so his remarks ring all too true! Check out his "FAQ". He's got a great function that replaces Access's normal AutoNumber.
Though I'm very impressed with his function (and the great commenting that is usually overlooked), I have one sore point I need to bring up about it :-( - it does not matter too much if you don't care if the Date/Time portion of the ID is in cronological order though :-D , since he does have an incrementation based on the actual values.
If, you live in an area that uses DayLight Savings Time (as most areas in the United States do)... On the days that you set time back an hour, your cronological order would be lost during that ONE time in the year. I'm sure for most users this does not matter much, but if you're designing a db that can plausibly have multiple transactions at 2:AM (I think that's when the time gets set back), you can't rely on the crono. order of transactions for that time period.
As this is such a minute period of time, only once a year, I think most people can safely neglect this. However, if you work somewhere, where you have a 24 hour operation, be wary of it!
Always consider DST when designing a system that leaves no room for error on the Date/Time stamp of records (such as Audit Logs).
MoGryph
- "Don't mess with my crew-cut [8O)"
- Status
Similar threads
- Locked
- Question
- Locked
- Question