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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can you AutoNumber starting at 40000 6
- Thread starter cc30729
- Start date
-
2
- #2
-
2
- #3
ZmrAbdulla
Technical User
Hi,
You don't have to trouble yourself by creating and deleting query etc etc...
Go to the design view of the table and select the AutoNumber field set the Format to \40000. You should use the back slash also. Remember your number will start from 40001 not from 40000. You have to use a number that ends with zero in the format.
Regards
Zameer Abdulla
You don't have to trouble yourself by creating and deleting query etc etc...
Go to the design view of the table and select the AutoNumber field set the Format to \40000. You should use the back slash also. Remember your number will start from 40001 not from 40000. You have to use a number that ends with zero in the format.
Regards
Zameer Abdulla
- Thread starter
- #4
I tried the \40000 method, but unfortunately the value was still 1,2,3 and so on although it did display as 40000.
However, I did also try the append query option which did work for what I wanted. This method did not change any of the existing numbers, but did accellerate the autocounter to 40000. Therefore, any existing records maintained their values and any new records started in the 40000 range.
Thanks for the input.
However, I did also try the append query option which did work for what I wanted. This method did not change any of the existing numbers, but did accellerate the autocounter to 40000. Therefore, any existing records maintained their values and any new records started in the 40000 range.
Thanks for the input.
ZmrAbdulla
Technical User
Hi,
You can download a sample from my site.
It is an Access 2000 file in zip
Regards
Zameer Abdulla
You can download a sample from my site.
It is an Access 2000 file in zip
Regards
Zameer Abdulla
This thread helped me as well
Jerome Benton
JERPAT Web Designs
GOD Is Good All The Time!!!
Jerome Benton
JERPAT Web Designs
GOD Is Good All The Time!!!
-
1
- #7
CC,
The problem, though, is not with how to do this, but with whether or not you should bother. And the answer is almost always no. You are using an autonumber value generated by Jet (the database engine in Access) to indicate some piece of information. I don't know what that piece of infomration is, but it is clear that you are trying to communicate something with the value of your autonumbers. And that's a _really_ bad idea. Jet creates unique autonumbers, and it creates them sequentially. But it does it very differently from the way you are expecting it to be done. Create a table with an autonumber field and one other. Create a record. The autonumber field will have a value of one. Create another record. Delete that record. Create another record. The value will not be two, it will be three. Now create another record, but don't save it, just hit the escape key twice. Now create another record: 5. Starting to see why it's such a bad idea to use autonumbers to convey information?
If you need to generate values with any significance whatsoever, you'll have to write code to do this, or crib it from soeone else, as it's a well-tackled problem. The best code I know of is in the best Access book I know: the Access [version number] Developer's Handbook.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
The problem, though, is not with how to do this, but with whether or not you should bother. And the answer is almost always no. You are using an autonumber value generated by Jet (the database engine in Access) to indicate some piece of information. I don't know what that piece of infomration is, but it is clear that you are trying to communicate something with the value of your autonumbers. And that's a _really_ bad idea. Jet creates unique autonumbers, and it creates them sequentially. But it does it very differently from the way you are expecting it to be done. Create a table with an autonumber field and one other. Create a record. The autonumber field will have a value of one. Create another record. Delete that record. Create another record. The value will not be two, it will be three. Now create another record, but don't save it, just hit the escape key twice. Now create another record: 5. Starting to see why it's such a bad idea to use autonumbers to convey information?
If you need to generate values with any significance whatsoever, you'll have to write code to do this, or crib it from soeone else, as it's a well-tackled problem. The best code I know of is in the best Access book I know: the Access [version number] Developer's Handbook.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
-
1
- #8
TonyJollans
Programmer
Hi cc30729,
Jeremy is right - do not try this at home. This is an often asked question and the 'requirement' almost always stems from a misunderstanding about what autonumbers are. If you really, really, really do have a solid reason then Jane's method is the one to use.
Zameer,
Your method only changes the way the number is displayed; it does NOT change the actual autonumber so if it is used as data anywhere its value is still, for example, 1, not 40001.
The format of "\40000" simply displays a 4 followed by the number using a minimum of 4 digits (the four zeroes), so ..
[tt]
1 displays as 40001
2 displays as 40002
:
:
9998 displays as 49998
9999 displays as 49999
10000 displays as 410000[/tt]
Enjoy,
Tony
------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
Jeremy is right - do not try this at home. This is an often asked question and the 'requirement' almost always stems from a misunderstanding about what autonumbers are. If you really, really, really do have a solid reason then Jane's method is the one to use.
Zameer,
Your method only changes the way the number is displayed; it does NOT change the actual autonumber so if it is used as data anywhere its value is still, for example, 1, not 40001.
The format of "\40000" simply displays a 4 followed by the number using a minimum of 4 digits (the four zeroes), so ..
[tt]
1 displays as 40001
2 displays as 40002
:
:
9998 displays as 49998
9999 displays as 49999
10000 displays as 410000[/tt]
Enjoy,
Tony
------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.
ZmrAbdulla
Technical User
Hi JaneC, Jeremy & TonyJollans,
You Programmers are right.The Number is displayed as 40001 is actually 1. I have done a test by concatenating and adding another field with this.
Result was:
40001 & 100 = 1100 We expect 40001100
40001 + 100 = 101 We expect 40101
Special thanks to JaneC
I should give star for all of you.
Regards
Zameer Abdulla
You Programmers are right.The Number is displayed as 40001 is actually 1. I have done a test by concatenating and adding another field with this.
Result was:
40001 & 100 = 1100 We expect 40001100
40001 + 100 = 101 We expect 40101
Special thanks to JaneC
I should give star for all of you.
Regards
Zameer Abdulla
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question