Autonumber with text

[rich]

i have a field in my table the Primary key this is in the form of an
autonumber i would like to prefix the auto generated number with a three
letter code to enable me to have several databases that i can combine at a
later date to differentiate which database the original record was from any
ideas how to do this .

i.e. i need the serial number to be BNL0001 for example

 

[aaron.kempf]

can't you just do this as a seperate field?

having a simple numeric (INTEGER) primary key is best for performance

 

[Sharkbyte]

Rich:

An Autonumber field, is just that...a number field. It will not allow you
to add text into the field. The only alternative, using your existing
structure, would be to (using a query) copy the ID field into another field,
and add your prefix at that point.

My suggestion would be to add a counter function yourself, and then
concatenate the text to that. You should be able to create a counter fairly
easily. The only tricky part, for those starting out, is handling the very
first record. Once you get past 00001, it is all downhill.

Good luck.

Sharkbyte

 

[Norman Yuan]

AutoNumber data type in Access is sort of "misleading" to average Access
users. It would be better to call it "AutoIdentity". It is only used for
providing a UNIQUE record ID, no meaning, sequence should be associated with
it.

For your requirement, you need to roll out your own computing logic to
generate your sequential number. With Access, you probably need to write
some code behind a form, which is used to add new records to the table. It
is not very difficult if you never need to delete a record, but if you need
to, then things may become a bit complicated, you need to think ahead: do
you want to fill the gap caused by deleting, do you need to rearrange the
serial number (if they are primary keys, how do you do that?).

 

[Chriske911]

i have a field in my table the Primary key this is in the form of an

autonumber i would like to prefix the auto generated number with a three
letter code to enable me to have several databases that i can combine at a
later date to differentiate which database the original record was from any
ideas how to do this .

i.e. i need the serial number to be BNL0001 for example

just add yet another field what states the database it is been made in
or any value you like so long as it is diff from the other databases
then use that field to create a combined index together with the data
ID for that table
when you import the data you can never have double records

grtz