I am keeping my test simple. Just use a text box with no special formating and its only goal is to start counting at 001 to 1000 ( This number should change with every new record that is made). But when it reaches 1000 it starts over the counting with 001. I have tried many methods and failed I will try what ever anyone comes up with.

Vince

Try Right("00"&Mod(Counter;1000);3)

Case( counter = 999, "1" ) & Right( "00" & NumToText(counter + 1), 3 )

Use it either in a Set Field when a new record is created or as an auto-enter calculation. Make counter a field in a preferences file which contains only one record and relate any files that require access to the counter using a constant calculation (or auto-enter) of 1.

Hmm. I read this as wanting 001 to 1000, then 001 again.

Otherwise you need to account for adding 2 so that 999 will go to 1001.

Right( "00" & NumToText(counter + 1 + (counter = 999)), 3 )

If you want 1000 to return 1, then the logic would be that a counter of 1 returns 2 I'd think.

Then Right("00"&(Mod(1+number,1000)),3) should do it.

You may explain the exact purpose of this though.

That still returns '000' instead of '001'.

Hmm...sounds I had it mixed up....

1+Mod(999+number;1000)

That removes the leading zeroes and gives the wrong number. Keep going.

Before we go too far...

Right("00"& 1+Mod(Mod(1000+number;1001);999);3)

You need to NumToText it and then it should work. Seems a bit more complicated than mine though.

Oh, and you need to add 1 to the number so it will increment.

Thank you this ticket is now closed.