I have misjudged the size of the digits I need for my primary key, and now I found I need to add more. My previous primary keys look like: P-000001, P-000002, and so on. However, now, it is approaching P-999999! I have the intention to add new digits to P-000XXXXXX. However, I need to keep the previous data with the new format.

I have attempted to use this query:

Update tblReturC Set Nomor = "P-000*" Where Nomor Like "P-*"

But it does not want to do it properly.

Is there any better ways to do it?

Thanks.

Hi,

You could try something like:

UPDATE tblReturC SET Nomor = Replace(Nomor, "P-", "P-000")

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

I presume this primary key is related to foreign keys in other tables? If so you should be sure that you have relationships with referential integrity enforced, and check the "CASCADE UPDATES" checkbox in the relationships window for all the relationships (including "grandchild" relationships, if there are any).

The Replace() function should work; for an alternative you could update it to

Left([NOMOR], 2) & "000" & Mid([NOMOR], 3)

Whichever way you do it back up the database first!

OK, guys. that works splendidly. And thank you for the advices too. Much appreciated.

Hi,

(IMG:style_emoticons/default/yw.gif)

John and I are happy to help. Good luck with your project.