Posted: July/06/2004 at 1:30am | IP Logged

To pad with leading 0's up to 4 digits:
varFld = Right("0000" & varFld, 4)

- - - - -

To generate the next invoice number using as an example 0003 (i.e. there are leading zeros):

Use max in a totals SQL statement:
strSQL = "SELECT Max(tblInv.InvID) AS InvIDMax FROM tblInv"

And then add one to the one record's one field:
NewID = objRS("InvIDMax") + 1

And then add padding:
NewID = Right("0000" & NewID, 4)

Posted: July/06/2004 at 12:35pm | IP Logged

Grabbing the next number in the database like that won't always work.

That is if your number ID field is a autonumber or auto increment field which it would most likely be.

For example when using MSACCESS if by chance the next number has been created and then deleted... (slim chance but it can happen)

If you use the method listed above you will get the wrong number because access will skip the one it deleted...  etc etc

I dont think that is an issue with SQL server though

There are also other scenarios when that will fail and get the wrong number.. like under multi user scenarios where another user might create a record right after you grab what you think is the next number... and before you actually create a record.

The best way to get the upcoming record number is of course to get it with your recordset at the same time you create that new record.

Your padding example though is clever and simple.