Making text behave as an autonumber

[Guest]

In a table I would like the records in one of the fields to contain the
following;

P00001, P00002 ........ P00999 ........ P09999, P10000

A combination of the letter 'P' and a 5 digit number starting at 00001 and
increasing in value by one with each new record. Its kind of like have an
autonumber field but with the inclusion of the letter 'P' at the start of the
number. The zeros are required following the 'P' so that the number portion
always has five digits.

Any ideas

 

[Joseph Meehan]

In a table I would like the records in one of the fields to contain
the following;

P00001, P00002 ........ P00999 ........ P09999, P10000

A combination of the letter 'P' and a 5 digit number starting at
00001 and increasing in value by one with each new record. Its kind
of like have an autonumber field but with the inclusion of the letter
'P' at the start of the number. The zeros are required following the
'P' so that the number portion always has five digits.

Any ideas

Two comments.

You don't need the "P" to show up in the table. If it is always a "P"
then just add it in the display in forms or reports. You should not be
using a table to display, enter or edit data. Tables are designed to store
data and define field properties.

It is will not always be a "P" you may want to consider a second field
for the text character.

Second I suggest you may not want to use Autonumber for that use.
Autonumbers are designed to provide unique numbers. It in not designed to
provide numbers in order and for a number of reasons may not do so. As a
result using them in any application where the user sees the numbers is
likely to end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Steve Schapel]

Slug,

I would certainly use an AutoNumber field for this. Then, when you need
it in the required format (which would be in a Form or Report - how it
appears in a table is normally never relevant because you don't normally
see it), you can set the Format property of the control to this...
\P00000

 

[Ron2006]

Just thoughts:
If you do decide to use an autonumber, remember that it may skip
numbers. Also, if you allow more than one person to add records to the
table, it is possible that numbers may be skipped. If two people start
to add a record, and the first one decides against adding a number, and
aborts the add process, (or their computer goes down or the network
goes down) then that number will be skipped.

If you use autonumber and a number of records are added and then it is
decided to delete those records, those numbers WILL NOT be used again.
This will occur even if no other new records were added.

If you use your own logic to keep track of the number, and the above
happens the numbers will be re-used IF no other new records are added.
If other records were added then the normal processing would not re-use
those numbers, however you could change the numbers for those few
records and therefore put them back in sequence with no skipped
numbers.

If you use your own logic to keep track of the number, you can
physically cause the system to skip a block of numbers, if so desired.
You CANNOT do this if you use autonumber.

Keep it in a control table on an always open form {hidden if you want}
and add 1 to it whenever a new record is created.

Basically, if you want to have ANY control of that number or ever want
to change one for any reason, you DO NOT want to use autonumber.

Ron

 

[Guest]

Thanks ALL