Hello,

I have searched the forum for the answer but cannot seem to find exactly what I am looking for.

I have a table with the PK auto assigned. Currently have about 150 records in it.

I would like to add a prefix to the Auto Assigned ID for all current and future records for an example if the record is 1 I want it to say GEN-1, GEN-2 etc..

I found this code however I cannot seem to integrate it into what I am trying to do.

Add a field that holds the date a record was added to the table. When you want to display a formatted autonumber field, use an expression.

Format(DateCreated,"yy") & "-" & Format(YourAutonumber,"000000")

This will produce something like -
03-000145

Thanks!

Fen How

If the prefix is always GEN- then you don't need to store it anyway.

Either use a calculated field in a query that looks like
MyFullID= "GEN-" & [MyAutonumber]

Or in any form or report use the same technique to add the prefix there.

I appreciate that, For the sake of reports etc that will work for me however I have three different companies using the same Database when I import the tables for all three companies into one primary DB I need to be able to seperate each companies records by way of the Data ID's.

If you have an alternative suggestion Please offer, I am looking for a good solution.

Fen

Ok - resolved -

Under format for the primary key with datatype = autonum

"M-"&@ will display as
M-1
M-2
M-3
M-4

Ok - resolved -

Under format for the primary key with datatype = autonum

"M-"&@ will display as
M-1
M-2
M-3
M-4

I have a feeling you may not be feeling so "warm and fuzzy" when you start having difficulties using it. You really shouldn't be doing it that way, as has been mentioned. I think you're missing the point that, if you want to display it in a particular way in a form, query or report, you can always set the format there. If you try to do it here it is likely going to cause you no end of hurt. Consider that a warning so that you can't say we didn't warn you.

Bob,

Yup, no warm and fuzzy going on here. That looked and worked like I wanted it to but once I really needed it to peform no way.

Perhaps you can shed some light, I am now trying to create a Primary key by merging two fields together an auto increment Number and a prefix. I just cannot get it to work.

I need a way to have three databases all the same structure etc.. each with unique record or ID numbers to show what company that record belongs to eventually wind up in one database where the brass can see everyone in one place.

I was working on the append query when I discovered my new found solution took a dive, as I went back to the forum searching and asking I found this and had to reply!

Thanks!
Fen

If you have 3 different databases, then you still don't need to store the prefix. If you insist on storing it, I would use two fields, one for the prefix and one for the autonumber. Then concatenate them when you need to.