Automatically Assign a number series 1

[WebGodiva]

I'm working on a database where I need to have the database assign a series of numbers for new records added to the table, i.e. 060013 and the next would be 060023, 060033 and so on and so on.

I've done searches here and i guess maybe i'm not looking for the right thing. Is there a way to accompish this?

any and all help would be appreciated...





"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[Remou]

The leading zero leads me to suppose this is a text field, after that, you seem to be adding 10. If this is the case, perhaps:
[tt]"06" & Format(Mid(DMax("Field1","tblTable"),3)+10,"0000")[/tt]

 

[nkailo]

Hi,

I have a similar query and am a complete novive to Access. I need to prepopulate fields with a series as follows: Lot1, Lot2 etc. Could you please provide a little more detail in the explanation. Thank you for your time.

Regards
Neil

 

[Remou]

Hi nkailo
"06" : The fixed part of the field, it is the same for all.

Then, from the middle:
DMax("Field1","tblTable") : Lookup the highest value of the relevant field (change Field1 to proper name) in the relevant table (change tblTable to proper name).

Mid(...,3)+10 : The number after the fixed (06) part. It stats at 3 in this case and continues to the end of the field. Add an increment, 10 in this case.

& Format( ... , "0000") : Formatting to get the filler zeros. That is display the result as four digits using zeros for missing digits on the left. This is essential because, in a text field, 2 comes after 10 but 002 comes before 010.

This can then be used to set the value of the appropriate field / control for new records.

 

[WebGodiva]

Thank you so much Remou, I find your postings to be quite relevant and knowledgeable - they are also very easy to understand. I've gone through quite a few of them and I truly appreciate your desire to assist us novices.

I am going to go now and try to implement this suggestion in my db.

again, many thanks



"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn