DMAX() - Text Field Issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe Williams - 31 Mar 2005 17:11 GMT

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

Reply to this Message

Dirk Goldgar - 31 Mar 2005 17:31 GMT

> 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.
[quoted text clipped - 6 lines]
> 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")

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

John Vinson - 31 Mar 2005 18:30 GMT

>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.
[quoted text clipped - 5 lines]
>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]

Reply to this Message