Trying to Set From Field Default Value as Dmax()+1

[vermette09]

I have a form created for entering new purchase orders. I want the form to default the value to the max purchase order number + 1 to represent the next available number. The problem is, because of the formatting of our purchase order numbers (yy-####, ex. 14-0123) it can't add "+1" to the value because it has to be a text field. Currently I have the default value set to DMax("Purchase_Order_Number","xtblPurchaseOrders") and it defaults to the most recent PO, but I was wondering if there was a way I could get it to add 1 to that number even though it is a text field. I can't seem to find anything with the same scenario as mine.

Your help is appreciated,


Josh

[lfpm062010]

Try,

MID(DMax("Purchase_Order_Number","xtblPurchaseOrde rs"), 1, 3) & FORMAT(CLNG(MID(DMax("Purchase_Order_Number","xtbl PurchaseOrders"), 4)) + 1, "0000")

[vermette09]

That works! I didn't notice the space between xtblPurchaseOrders on the second part, my bad. Thank you very much!

Best,
Josh

[vermette09]

Post Removed...

[lfpm062010]

The "0000" is part of the "FORMAT" to convert the number back to text field with leading zero.

http://www.techonthenet.com/access/f...ing/format.php