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 Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
re:Leading zero's and auto number
- Thread starter cdl1701
- Start date
You don't. Autonumbers are long integers and they don't store leading or trailing (after the decimal place) zeros. If you want to portray these things with leading zeros they you need to use format to make text strings like this
Format(AutoNumField, "00000")
Format(AutoNumField, "00000")
- Thread starter
- #3
I was assuming that you were building queries on the table. If you want the table display to show leading zeros then open the table in design view and, for the autonum field, set the "Format" property to 00000 (no quotes around it) or however many total digits you want to appear.
Hi Leslie
Golom, it has been eons!
cdl1701
My "generic" approach...
Richard
Golom, it has been eons!
cdl1701
My "generic" approach...
Richard
- Thread starter
- #7
Ok, Lets assume for a moment that I know very little about access or programming. (Not an assumption. reality) where do I place this code and how do I do it. Yes, I know you all are rolling your eyes thinking this guy needs to get a clue.=) This is the last part that I need to finish up my project log data base and I am to clueless to figure it out. =(
Thank you again for everyones replies
Chris
Thank you again for everyones replies
Chris
create a new query (don't use the design grid, switch to the SQL view)
paste the following:
SELECT "T" & Format(AutoNumField, "00000") FROM tblName
correct:
tblName to your actual table's name
AutoNumField to your actual field name
Run the query. It will give you the autonumber field formatted with the leading 0's and a "T" at the beginning of the number.
Leslie
paste the following:
SELECT "T" & Format(AutoNumField, "00000") FROM tblName
correct:
tblName to your actual table's name
AutoNumField to your actual field name
Run the query. It will give you the autonumber field formatted with the leading 0's and a "T" at the beginning of the number.
Leslie
- Thread starter
- #9
- Thread starter
- #11
Didn't Golom alredy address/solve the issue (their second reply)?
If you're using an Autonumber (or any integer number datatype), then it stores only the number, not leading digits. But you can format it to display whatever you like.
To display (format) an autonumber (or any number) on a form or report, with leading digits, you don't need any query, just enter your format string into the format property of the control you're displaying the field in (or as Golom suggested, in the field properties in the table - then you'd need to delete the current control showing the autonumber on the form/report, and add it again from the field list, to reinherit the new property)
So for the numbers 1 and 555:
[tt]Format Displayed Stored
1 555 1 555
00000 -> 00001 00555 1 555
"T-"00000 -> T-00001 T-00555 1 555[/tt]
Roy-Vidar
If you're using an Autonumber (or any integer number datatype), then it stores only the number, not leading digits. But you can format it to display whatever you like.
To display (format) an autonumber (or any number) on a form or report, with leading digits, you don't need any query, just enter your format string into the format property of the control you're displaying the field in (or as Golom suggested, in the field properties in the table - then you'd need to delete the current control showing the autonumber on the form/report, and add it again from the field list, to reinherit the new property)
So for the numbers 1 and 555:
[tt]Format Displayed Stored
1 555 1 555
00000 -> 00001 00555 1 555
"T-"00000 -> T-00001 T-00555 1 555[/tt]
Roy-Vidar
cdl1701
Golom / Leslie / RoyVidar / PHV have presented one solution where you use the autonumber feature for numbering a record, and format the number to your requirements. Since you seem to want the number to display per your format requriements, then use a function...
For example, pretend the following field / column variables match the ones you use in your database...
SELECT MyFormat([Job Number]), JobDate, Contractor from JobTable
Since you have created MyFormat as a public function, the function will format the number for you.
Then you can call the format from anywhere, including within a query. If you need to change the formatting, for example "T" -> "U", you make the change at one location.
Having said all this, if you want to store the number without formatting, meaning store the number as a text string, then you may wish to use an administrative table to store control numbers as per my aforementioned link.
Please note:
0000123 - has to be stored as a text string
123 - can be stored as a string or a number
Adding numbers is a heck of a lot easier than adding strings (which requires the extraction of the number, perform the math and convert the number back to a string).
Lastly, sometimes we like our numbers to be ordered / in sequence and we forget that we really just want the numbers to identify something - in your case a Job Number. Developers and programmers would consider having sequential numbers that are used solely for identification as "cosmetic", where the sequential numbering is not a really necessary.
The exception to this is when sequential numbers is used for control / audit purposes -- cheque numbers, invoice numbers. In this case, a missed number can be a cause for concern.
If your purpose is to use sequential numbers for control purposes, the use of auonumbers is not without issues. In a multi-user database, you can experience problems, and even with a single user database, may find that you loose the occasional number. There are numerous posts on AutoNumber issues. Here is a test, start to create a Job record. Notice that the autonumber will be displayed as soon as you start to enter the record. Now hit the escape key twice - the record clears. Now start to enter a new record -- the autonumber will have advanced to the next number -- you just lost a number. This is not a "big thing" if you are using numbers for identification. If the numbers are to be used for control, now you may have to document what happened. (Which is why for this type of activity, I use a control table)
Golom / Leslie / RoyVidar / PHV have presented one solution where you use the autonumber feature for numbering a record, and format the number to your requirements. Since you seem to want the number to display per your format requriements, then use a function...
Code:
Function [COLOR=blue]MyFormat([Job Number] as Long)[/color] as String
MyFormat = "T" & Format([JOB NUMBER], "00000")
End FunctionFor example, pretend the following field / column variables match the ones you use in your database...
SELECT MyFormat([Job Number]), JobDate, Contractor from JobTable
Since you have created MyFormat as a public function, the function will format the number for you.
Then you can call the format from anywhere, including within a query. If you need to change the formatting, for example "T" -> "U", you make the change at one location.
Having said all this, if you want to store the number without formatting, meaning store the number as a text string, then you may wish to use an administrative table to store control numbers as per my aforementioned link.
Please note:
0000123 - has to be stored as a text string
123 - can be stored as a string or a number
Adding numbers is a heck of a lot easier than adding strings (which requires the extraction of the number, perform the math and convert the number back to a string).
Lastly, sometimes we like our numbers to be ordered / in sequence and we forget that we really just want the numbers to identify something - in your case a Job Number. Developers and programmers would consider having sequential numbers that are used solely for identification as "cosmetic", where the sequential numbering is not a really necessary.
The exception to this is when sequential numbers is used for control / audit purposes -- cheque numbers, invoice numbers. In this case, a missed number can be a cause for concern.
If your purpose is to use sequential numbers for control purposes, the use of auonumbers is not without issues. In a multi-user database, you can experience problems, and even with a single user database, may find that you loose the occasional number. There are numerous posts on AutoNumber issues. Here is a test, start to create a Job record. Notice that the autonumber will be displayed as soon as you start to enter the record. Now hit the escape key twice - the record clears. Now start to enter a new record -- the autonumber will have advanced to the next number -- you just lost a number. This is not a "big thing" if you are using numbers for identification. If the numbers are to be used for control, now you may have to document what happened. (Which is why for this type of activity, I use a control table)
- Thread starter
- #14
- Thread starter
- #15
There are two ways:
1. Delete the AutoNumber field from the table, save it and then recreate it. There's no guarantee about which records will receive which numbers.
2. Delete all data from the table (Delete * From tbl) and then compact the database.
1. Delete the AutoNumber field from the table, save it and then recreate it. There's no guarantee about which records will receive which numbers.
2. Delete all data from the table (Delete * From tbl) and then compact the database.
Ouch, ouch, ouch - you're giving Autonumbers business meaning - should have commented on that before.
I think the common understanding among developers is that when using Autonumbers
* do not let them have any business meaning
* best not expose them to the users (or the other way around;-)) use them only for referential integrity (relating tables)
Why?
* they are only unique numbers to identify the record (not always unique either;-))
* they are not sequental numbers, when one record is cancelled, it leaves a gap
* you cannot reuse "deleted" numbers
If you are going to let Autonumbers have business meaning, you are heading for headaches... You should perhaps look a this faq by MichaelRed's faq faq700-184 for another method of creating unique numbers.
All that said, if all records are deleted, a compact/repair should do. Else have a look at this workaround from Microsoft for an Access 2003 Autonumber bug BUG: You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2003
Roy-Vidar
I think the common understanding among developers is that when using Autonumbers
* do not let them have any business meaning
* best not expose them to the users (or the other way around;-)) use them only for referential integrity (relating tables)
Why?
* they are only unique numbers to identify the record (not always unique either;-))
* they are not sequental numbers, when one record is cancelled, it leaves a gap
* you cannot reuse "deleted" numbers
If you are going to let Autonumbers have business meaning, you are heading for headaches... You should perhaps look a this faq by MichaelRed's faq faq700-184 for another method of creating unique numbers.
All that said, if all records are deleted, a compact/repair should do. Else have a look at this workaround from Microsoft for an Access 2003 Autonumber bug BUG: You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2003
Roy-Vidar
YouSaid said:
In my last post, I discussed the issue about sequential numbering.
ISaid said:
It sounds like you are using the "T" number as a control. Or you are hung up on having the numbers being sequential. RoyVidar has supplemented this point.
If you decide to use Autonumbers, you will loose some numbers.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
