format problem with increasing a number

[Ju]

I need to increase a field which is 6 number long. I need to increase it by 1 each time I change of record.<br>My problem is that I can't keep the leading 0 (zeros) of my number when I increase it.<br>So here is what I want:<br>1st record : 000001<br>2nd record : 000002 <br>etc...<br>10th record: 000010<br>etc....<br><br>Thank you.

 

[Vandys]

You cannot have this field as a number data type. One way I can think of doing this is that make this field as a number field and make another field as a text field which will be 6 char long. When updating the record profix the filed with the number of zeros required and then update in the field.<br><br>for ex. if it is 110th record then the field will have value as 110 and the other field can be updated as 000110.<br>Suppose the field which stores the rcord number is testno then:-<br>set dbs=currentdb()<br>set rst=dbs.openrecordset(&lt;the name of the table&gt<br>varId = Str(rst!testno)<br>Do While Len(varId) &lt;&gt; 7<br>&nbsp;&nbsp;&nbsp;&nbsp;varId = &quot;0&quot; + varId<br>Loop<br>rst.MoveNext<br>Next i<br><br>update this value of varId in a text field of 6 char length.<br><br>Vandys

 

[PaulF]

In Design Mode for the table, set the Format Property for the field to 000000<br><br>or if you are doing it in code use <br><br>Format([YourFieldName],

 

[PaulF]

the previous post was suppose to read<br><br>or if you are doing it in code use <br><br>&nbsp;Format([YourFieldName], &quot;000000&quot<br><br>PaulF