Auto increment a field - access 2000

[offmyrocker]

Anyone know how to auto increment a text field? For example, I'm trying to auto increment "t001" and so on in design view table.

 

[fneily]

This has been answered hundreds of times. But to avoid a search:
Let’s say you want an autonumbered field by Microsoft but want some characters in front of the number like mc001, mc002, etc. Go to design view, click on the autonumber field and in the Format box type “mc”00
This places mc next to 2 zeros and then tacks on the autonumber.

 

[offmyrocker]

Thats the job!
Thanks for your help..

 

[lespaul]

now realize that you may end up with missing numbers if you use the autonumber. For instance, if you start to add a record and then back out, you've used an autonumber and you'll be missing a number in the sequence:

MC001
MC002
MC004 {missing 3}

if you need sequential and consecutive, you'll need to do something different.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[fneily]

LesPaul is right (love him with Mary Ford!)
So you can:
Let’s say you want to create your own autonumber field. Create a table with a field called ID. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

 

[mondeoman]

Sorry OffMyRocker to butt in but this is exactly what I want to do as well but I can't get fneily's idea to work althought it would be great if it did.

I have a field in my table "tbl_Support_Request" called "Job_Purchase_Number" its data type is Number. I have a form called "frm_Support Request" that also includes the field "Job_Purchase_Number". In the Default Value of the field "Job_Purchase_Number" I have =DMax([Job_Purchase_Number],"tbl_Support_Request")+1 but all I get when the form is opened is #error. What am I doing wrong?

Mondeoman

 

[fneily]

=DMax("[Job_Purchase_Number]","tbl_Support_Request")+1
Try it with the above quotes around the first argument. Remember, you can highlight a function name, eg. DMax, then hit the F1 key and it will show you the syntax.