I have a query with which I am returning the MAX value of a column. I want to increment that value by 1 and insert the new value into my database. However, the value is a string that has two alpha a three numeric values i.e. AA001, AA002.

For example, if my query returns a MAX value of AA002, I want to increment by 1 to AA003; or if the MAX value is AA011, I want to increment by 1 to AA012.

However using the following logic:

(LEFT('AA002', 2)) & (RIGHT('AA002',3) + 1)
(LEFT('AA002', 2)) & (RIGHT('AA002',3) + NumberFormat(1 [, 999 ])
(LEFT('AA002', 2)) & IncrementValue(RIGHT('AA002',3))

creates a value of AA3 not AA003 as intended.


Thanks for the help,
~Calvert