DMAX() - Text Field Issue

[Joe Williams]

I have a field in our database that is a TEXT field that has numeric values
in it stored as follows: 000013, 000014, 000015, 000016, etc.

I want to be able to add a new record and have the next velu be 000017. I
tried using dmax() + 1 and it gives the next record as 17 and not 000017.

This was a pre-existing database and I cannot change the data type on this
field but I need the functionality of finding the highest number and storing
it as the text value 000017. How can I do it?

Thanks

Joe

 

[Dirk Goldgar]

I have a field in our database that is a TEXT field that has numeric
values in it stored as follows: 000013, 000014, 000015, 000016, etc.

I want to be able to add a new record and have the next velu be
000017. I tried using dmax() + 1 and it gives the next record as 17
and not 000017.

This was a pre-existing database and I cannot change the data type on
this field but I need the functionality of finding the highest number
and storing it as the text value 000017. How can I do it?

Try this:

Format(DMax("Val(Nz([TheField], 0))", "TheTable") + 1, "0000000")

 

[John Vinson]

I have a field in our database that is a TEXT field that has numeric values
in it stored as follows: 000013, 000014, 000015, 000016, etc.

I want to be able to add a new record and have the next velu be 000017. I
tried using dmax() + 1 and it gives the next record as 17 and not 000017.

This was a pre-existing database and I cannot change the data type on this
field but I need the functionality of finding the highest number and storing
it as the text value 000017. How can I do it?

Set it to

Format(Dmax() + 1, "000000")

The Format function converts a number to a String, in this case with
leading zeros.


John W. Vinson[MVP]