Combining DMAX with a Format() number

D

DubboPete

Hi all,

I have learned how to format a number from 1 to 0001, using the
Format([nextno],"0000") method, and I was wondering how to get that combined
with :

Me.[nextno] = DMax("[nextno]", "[TblNextNumber]") + 1

therefore getting the maximum number plus an increment of one from the
table, and formatting it to "0000".

Is this possible?

Currently I have table TblNextNumber which stores the numbers in field
[nextno]
For arguments sake, the last number used was 34
I want to get the next number and make it display 0035 in the field [nextno]
on my form.

Any help appreciated

DubboPete
 
K

Klatuu

Glad you solved it, but for future reference:

Format(Me.[nextno] = Nz(DMax("[nextno]", "[TblNextNumber]"),0) + 1,"0000"

Note the addition of the Nz function. The reason is that if, for any
reason, the DMax returns a Null value (for example, an empty table), it will
convert it to 0 rather than throw an error. Since it converts to 0, the
first number you would get would be 1.
 
Top