DMAX() - Text Field Issue

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

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
 
Joe Williams said:
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?
Click to expand...

Try this:

Format(DMax("Val(Nz([TheField], 0))", "TheTable") + 1, "0000000")
 
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?
Click to expand...

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]
 
Back
Top