Adding a number to a field

[Datafiend]

Hi there
I have a table with 100,000 plus unique rows and I want to add a column (call it 'ID') and place a value in each row starting with 000001 and adding one on to each row.

Is anyone able to show me the correct code to do this. I've tried with various combinations of For...next but can't get anything to work.

Thanks in advance for any help you can offer.

datafiend

 

[plog]

First, 000001 is not a number; 1 is.

Second, what's the purpose of this? What will you be using this field for?

Third, does your data have an inherent order? Does one record come before the other? If so, what field determines this order?

Last and most importantly, why not just add an autonumber field?

 

[CJ_London]

Ours is not to reason why

Try this:

1. Open the table in design view
2. add a new field called ID
3. set its type to autonumber
4. set its format to "000000"
5. save the table

if you actually want a text id then following on from above

1. open the table in design view and add a new field called TextID
2. set its type to text
3. save the table
4. create a query with the following SQL (change names to suit)

5. UPDATE myTable1 SET txtid = Format([id],"000000")

6. run the query
7. open the table in design view
8. delete the ID field
9. rename the txtID field as ID

 

[Datafiend]

Thanks very much CJ_London and plog for your replies. CJ, your solution worked fine. I had tried autonumber but didn't know I could set it to start at 000...

datafiend