DMax + 1 (using minimum number)

[adh123]

I need to create a fixed length ID field in our access system (will be exported into the accounts system so needs to be a 5 digit number). Table does not have any records to date. Other than entering the first line manually, is there a piece of code which will check to see if table is empty, if so then start at (e.g. A10001) and then for future entries it adds 1 (A10002, A10003 etc)

Also, would DMax("q_id", "tblAccounts") + 1 recognise the A? So record A99999 would be followed by B10000? (this is not required but might be nice to have, the preceding letter can be dropped if it will cause issues with DMax!)

Thanks in advance!

 

[CJ_London]

Have you thought about actually just trying it before asking the question?

Logic dictates that 'A10001' is a string, and you can't add strings like numbers

Drop the A (and set the field type as a number) and DMax+1 will work - subject to any multiuser timing issues and seeding the first number of 10000

 

[GinaWhipp]

Have a look here...
http://regina-whipp.com/blog/?p=704

 

[adh123]

Thanks, Gina I think I could use the example for 2 digit year followed by an increasing number, so that would get around the 'first record' issue .

CJ_London, I had tried and failed. Not having the vast experience (in fact, very little experience!) that others on this forum have I thought I would ask those who would be able to suggest a good method, rather than me fudging it and it breaking something else further down the line.
Thanks for the suggestion, I will drop the A and have a work around in mind for this

 

[GinaWhipp]

Great, glad that helped!

 

[Solo712]

I need to create a fixed length ID field in our access system (will be exported into the accounts system so needs to be a 5 digit number). Table does not have any records to date. Other than entering the first line manually, is there a piece of code which will check to see if table is empty, if so then start at (e.g. A10001) and then for future entries it adds 1 (A10002, A10003 etc)

Also, would DMax("q_id", "tblAccounts") + 1 recognise the A? So record A99999 would be followed by B10000? (this is not required but might be nice to have, the preceding letter can be dropped if it will cause issues with DMax!)

Thanks in advance!

Sure,
assuming you read Gina's expose and you still want to do it, there is a way to do it. One thing though needs to be sorted out. If the numeric suffix is fixed length five digits, it is not clear how the next in the series from "A99999" is "B10000". Logically, one would expect "B00000" or "B00001". So, you need to provide the incremental rules for generating the number.

You can use the DMax function to determine the largest account number even if it is a string, provided that the leading letters are in sequence and the numerical suffix is of fixed length. You cannot increment the string directly but you can separate the numerical suffix, increment it and rebuild the string. But again, an algorithm is needed here.

Best,
Jiri

 

[adh123]

Great, glad that helped!

Adapting the examples for creating a 2 digit year, month, day + "-" and number I have ended up with the below, which is now working:

Me.txtq_id = Format(Me.txtq_date_created, "YY") & Format(Me.txtq_date_created, "MM") & Format(Nz(DLookup("CountOfQuotes", "qryQuoteCount", "Quotes= " & Format(Me.txtq_date_created, "YY") & Format(Me.txtq_date_created, "MM")), 0) + 1, "00")

Thanks!