Increment alpha-numeric value

[chuckgoss]

Hi all...

I have searched this and other forums for how to do this. Found some hits regarding similar problems, but I just can't make it work.

I am trying to increment a contract number (beginning with the one of greatest numerical value in my table) by 1 whenever a new contract number is needed.

The difficulty I have is the existing number format is always supposed to begin with the prefix 'EMC-'. The EMC- is always followed by a four digit number. If the most recient number is for example: EMC-1902, then I need to increment that to EMC-1903 ... and so on. I would like to have an unbound control show the new contract number so that I could add the record to the table programaticly (that part I can do), however, if it's easier to do it another way, then so be it.

The name of the table that contains the existing contract numbers is: tblContract_Header, and the field in it that contains the numbers is called: Contract_Number

Thanks for your thoughts,

chuck

 

[WayneRyan]

Chuck,

Haven't tried it, but:

NewNumber = Mid(OldNumber, 1, 4) & Str(Val(Mid(OldNumber, 5, 4) + 1))

Wayne

 

[chuckgoss]

Wayne!

It's works SOOOO... good!

It increments great. I tried it also on earlier contract numbers that have the numerical piece begin with '0' such as:
EMC-0814, and, unusually enough, it comes back with EMC-815, not showing the zero.

I don't think I'll need to deal with that phenomenon in this project, but if you know a way around that issue, I'd be grateful to hear it so that I may put it in my bag of tricks to use some other time.

Thanks so much!

chuck

 

[Pat Hartman]

For the future, store the alpha and numeric portions in separate fields. Then you won't have to pick out the numeric characters to operate on them. To show the contract number formatted, concatenate the two parts and use the Format() function if you want to show leading zeros in the numeric portion.

MyTextPart & Format(MyNumericPart,"0000") As ContractNum

Adjust the number of 0's in the Format() mask to specify the length of the numeric portion.

 

[chuckgoss]

Thanks Pat!