Increment varibale length alpha numeric field by 1

[jeffj803]

Increment variable length alpha numeric field by 1

I have a database that is used for tracking changes to numerous courses. Part of this database create a unique tracking number for each course problem developed. Currently I have the form do a comparison using the highest most number to compare against the current number assigned and prevent the form from saving until the number is incremented and not a duplicate, it would be a lot easier if I could just have it increment plus 1. I have seen various answers but they all seem to depend on the alpha portion of the field being a set value, in my instance it is variable in length. The only part that is fixed is the last four characters to the right which are the numeric portion I would like to increment. For example the field can equal:
QACP-M-PIQ-6059
QACP-M-PREF-6002

Any thoughts on how to extract just the numeric portion, increment it by one and save?

Thank you for any help you can provide,
Jeff

 

[jdraw]

Have you tried Val(yourFieldName) +1?
see http://www.techonthenet.com/access/functions/numeric/val.php

 

[jeffj803]

Re: Increment variable length alpha numeric field by 1

Got it figured out, here is what I did:

QACPlength = Len([Combo116])
Pre = Left([Combo116], [QACPlength] - 4)
Post = Right([Combo116], 4) + 1
Me.QACPctrl.Value = Pre & Post

Got the length of the string using the length command
Saved the alpha portion of the string into the Pre varible
Saved the numberic value + 1 into the Post variable
Set the form text box equal to Pre + Post

Hope this helps someone else out along the way