Problem with how integer is displayed

[craigcain]

Hi,

This has probably been discussed before but i didnt know what to search for...

I use the following code on a form to check for the number of orders in a table and then add 1 to create the next order number:

=Count(OrderDetails!ID)+1

This works fine but displays the number as an integer and gets rid of any leading zero's. E.g. 1 instead of 0001.

I have been asked to make the number appear as 4 characters. Is there a way to do this?

Thanks for any help

 

[KenHigg]

What happens if you are up to say 100, then you create order 101 and the an order gets deleted. If you looked athe number of orders you'd be back at 100 and your logic would then create order 101 a second time.

???

 

[Guus2005]

Use the format statement:

 format(expression, "000#")

 

[chergh]

try:

right("000" & count(orderdetails!ID)+1,4)

 

[craigcain]

What happens if you are up to say 100, then you create order 101 and the an order gets deleted. If you looked athe number of orders you'd be back at 100 and your logic would then create order 101 a second time.

???

This would allow up to 9999 orders. After that its not my problem, it was asked to be done this way

 

[CyberLynx]

Like Guus2005 has posted:

=Format(Count(OrderDetails!ID)+1, "000#")

.

 

[KenHigg]

This would allow up to 9999 orders. After that its not my problem, it was asked to be done this way

You missed the point. Doing a count is a bad idea...

 

[Darth Vodka]

You missed the point. Doing a count is a bad idea...

how abouts a Dmax...?

 

[KenHigg]

Better but still not sound. If you created a record with order number 100 and printed a order sheet, etc. Then deleted the order, you could create another completely different order with that same number...

I think I'd just have custom system table to store the value and increment it by one every time an order is created...

Having said all of that ( ) I'd have it set up where the system never deleted an order record from the table but rather 'mark' it as deleted if needed...

 

[Rich]

Better but still not sound. If you created a record with order number 100 and printed a order sheet, etc. Then deleted the order, you could create another completely different order with that same number...

I think I'd just have custom system table to store the value and increment it by one every time an order is created...

Having said all of that ( ) I'd have it set up where the system never deleted an order record from the table but rather 'mark' it as deleted if needed...

Better to actually mark it as archived than deleted